Concatenating variable across observations

Hi, I would appreciate it if someone could help me with problem. I need to add a variable to the dataset that is a concatenation of the UNIT_ID variable by SALES_DATE. Example - for 01SEP06 I need a new unique id of 2100-3557. Obs PERSON_ID SALES_DATE UNIT_ID 137 77 01SEP06 2100 138 77 01SEP06 3557 139 77 05NOV06 1454 140 77 05NOV06 3765 141 77 13NOV06 1345 142 77 13NOV06 1456 Thanks, Mike

Mike, Would something like the following suffice?: data have; informat sales_date date7.; format sales_date date7.; input PERSON_ID SALES_DATE UNIT_ID; cards; 77 01SEP06 2100 77 01SEP06 3557 77 05NOV06 1454 77 05NOV06 3765 77 13NOV06 1345 77 13NOV06 1456 ; proc transpose data=have out=want (drop=_:); var unit_id; by person_id sales_date; run; data want (drop=col:); set want; unit_ids=catx('-',of col:); run; HTH, Art ------- On Mon, 11 Jan 2010 15:28:05 -0500, Mike Green <mike_green123@YAHOO.COM> wrote: >Hi, > >I would appreciate it if someone could help me with problem. > >I need to add a variable to the dataset that is a concatenation of the >UNIT_ID variable by SALES_DATE. Example - for 01SEP06 I need a new unique >id of 2100-3557. > > Obs PERSON_ID SALES_DATE UNIT_ID > > 137 77 01SEP06 2100 > 138 77 01SEP06 3557 > 139 77 05NOV06 1454 > 140 77 05NOV06 3765 > 141 77 13NOV06 1345 > 142 77 13NOV06 1456 > >Thanks, > >Mike

Thank you Arthur, I appreciate your help. Mike On Mon, 11 Jan 2010 16:09:26 -0500, Arthur Tabachneck <art297@NETSCAPE.NET> wrote: >Mike, > >Would something like the following suffice?: > >data have; > informat sales_date date7.; > format sales_date date7.; > input PERSON_ID SALES_DATE UNIT_ID; > cards; > 77 01SEP06 2100 > 77 01SEP06 3557 > 77 05NOV06 1454 > 77 05NOV06 3765 > 77 13NOV06 1345 > 77 13NOV06 1456 >; > >proc transpose data=have out=want (drop=_:); > var unit_id; > by person_id sales_date; >run; > >data want (drop=col:); > set want; > unit_ids=catx('-',of col:); >run; > >HTH, >Art >------- >On Mon, 11 Jan 2010 15:28:05 -0500, Mike Green <mike_green123@YAHOO.COM> >wrote: > >>Hi, >> >>I would appreciate it if someone could help me with problem. >> >>I need to add a variable to the dataset that is a concatenation of the >>UNIT_ID variable by SALES_DATE. Example - for 01SEP06 I need a new unique >>id of 2100-3557. >> >> Obs PERSON_ID SALES_DATE UNIT_ID >> >> 137 77 01SEP06 2100 >> 138 77 01SEP06 3557 >> 139 77 05NOV06 1454 >> 140 77 05NOV06 3765 >> 141 77 13NOV06 1345 >> 142 77 13NOV06 1456 >> >>Thanks, >> >>Mike

Mike, Here is a solution that concatenates all the IDs (as stated), in a single data step: data want; do _N_=1 by 5; set have; by person_id sales_date; length unit_ids $200; substr(unit_ids,_n_,4)=put(unit_id,4.0); if last.sales_date then leave; substr(unit_ids,_n_+4,1)='-'; end; drop unit_id; run; However, if you only have two UNIT_IDs for each SALES_DATE, or if you only want the range (first and last unit_id) then this will do: data want2; set have; by person_id sales_date; retain unit_range ' - '; if first.sales_date then substr(unit_range,1,4)=put(unit_id,4.0); if last.sales_date; substr(unit_range,6)=put(unit_id,4.0); drop unit_id; run; NB: Both solutions are assuming that your UNIT_IDs have 4 digits. Regards, Søren On Mon, 11 Jan 2010 15:28:05 -0500, Mike Green <mike_green123@YAHOO.COM> wrote: >Hi, > >I would appreciate it if someone could help me with problem. > >I need to add a variable to the dataset that is a concatenation of the >UNIT_ID variable by SALES_DATE. Example - for 01SEP06 I need a new unique >id of 2100-3557. > > Obs PERSON_ID SALES_DATE UNIT_ID > > 137 77 01SEP06 2100 > 138 77 01SEP06 3557 > 139 77 05NOV06 1454 > 140 77 05NOV06 3765 > 141 77 13NOV06 1345 > 142 77 13NOV06 1456 > >Thanks, > >Mike

On Jan 12, 1:28=A0am, mike_green...@YAHOO.COM (Mike Green) wrote: > Hi, > > I would appreciate it if someone could help me with problem. > > I need to add a variable to the dataset that is a concatenation of the > UNIT_ID variable by SALES_DATE. Example - for 01SEP06 I need a new unique > id of 2100-3557. > > =A0 =A0 =A0 =A0 =A0 Obs =A0 =A0 =A0 =A0PERSON_ID =A0 =A0SALES_DATE =A0UNI= T_ID > > =A0 =A0 =A0 =A0 =A0 137 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A001SEP06 =A0= =A0 2100 > =A0 =A0 =A0 =A0 =A0 138 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A001SEP06 =A0= =A0 3557 > =A0 =A0 =A0 =A0 =A0 139 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A005NOV06 =A0= =A0 1454 > =A0 =A0 =A0 =A0 =A0 140 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A005NOV06 =A0= =A0 3765 > =A0 =A0 =A0 =A0 =A0 141 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A013NOV06 =A0= =A0 1345 > =A0 =A0 =A0 =A0 =A0 142 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A013NOV06 =A0= =A0 1456 > > Thanks, > > Mike Hi, The following code would work for u. proc sort data=3Dx; by sales_date; run; data y; set x; by sales_date; length newvar $100; retain newvar; if first.sales_date then newvar=3Dunit_id; else newvar=3Dnewvar||unit_id; if last.sales_date then output; run;

On Jan 12, 11:28=A0am, Kulpreet Khanna <kulpreetkha...@gmail.com> wrote: > On Jan 12, 1:28=A0am, mike_green...@YAHOO.COM (Mike Green) wrote: > > > > > > > Hi, > > > I would appreciate it if someone could help me with problem. > > > I need to add a variable to the dataset that is a concatenation of the > > UNIT_ID variable by SALES_DATE. Example - for 01SEP06 I need a new uniq= ue > > id of 2100-3557. > > > =A0 =A0 =A0 =A0 =A0 Obs =A0 =A0 =A0 =A0PERSON_ID =A0 =A0SALES_DATE =A0U= NIT_ID > > > =A0 =A0 =A0 =A0 =A0 137 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A001SEP06 = =A0 =A0 2100 > > =A0 =A0 =A0 =A0 =A0 138 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A001SEP06 = =A0 =A0 3557 > > =A0 =A0 =A0 =A0 =A0 139 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A005NOV06 = =A0 =A0 1454 > > =A0 =A0 =A0 =A0 =A0 140 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A005NOV06 = =A0 =A0 3765 > > =A0 =A0 =A0 =A0 =A0 141 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A013NOV06 = =A0 =A0 1345 > > =A0 =A0 =A0 =A0 =A0 142 =A0 =A0 =A0 =A0 =A0 =A0 =A0 77 =A0 =A013NOV06 = =A0 =A0 1456 > > > Thanks, > > > Mike > > Hi, > The following code would work for u. > proc sort data=3Dx; > by sales_date; > run; > > data y; > set x; > by sales_date; > length newvar $100; > retain newvar; > > if first.sales_date then newvar=3Dunit_id; > else newvar=3Dnewvar||unit_id; > > if last.sales_date then output; > > run;- Hide quoted text - > > - Show quoted text - Please use trim function while concatenating.newvar=3Dtrim(newvar)||trim (unit_id);

> -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of > Arthur Tabachneck > Sent: Monday, January 11, 2010 1:09 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: Concatenating variable across observations > > Mike, > > Would something like the following suffice?: > > data have; > informat sales_date date7.; > format sales_date date7.; > input PERSON_ID SALES_DATE UNIT_ID; > cards; > 77 01SEP06 2100 > 77 01SEP06 3557 > 77 05NOV06 1454 > 77 05NOV06 3765 > 77 13NOV06 1345 > 77 13NOV06 1456 > ; > > proc transpose data=have out=want (drop=_:); > var unit_id; > by person_id sales_date; > run; > > data want (drop=col:); > set want; > unit_ids=catx('-',of col:); > run; > > HTH, > Art > ------- > On Mon, 11 Jan 2010 15:28:05 -0500, Mike Green <mike_green123@YAHOO.COM> > wrote: > > >Hi, > > > >I would appreciate it if someone could help me with problem. > > > >I need to add a variable to the dataset that is a concatenation of the > >UNIT_ID variable by SALES_DATE. Example - for 01SEP06 I need a new unique > >id of 2100-3557. > > > > Obs PERSON_ID SALES_DATE UNIT_ID > > > > 137 77 01SEP06 2100 > > 138 77 01SEP06 3557 > > 139 77 05NOV06 1454 > > 140 77 05NOV06 3765 > > 141 77 13NOV06 1345 > > 142 77 13NOV06 1456 > > > >Thanks, > > Mike, Here is one more option: data want; do until(last.sales_date); set have; by person_id sales_date; **length depends on max number of UNIT_ID per person/unit; length unit_ids $100; if first.sales_date then unit_ids = cats(unit_ids,unit_id); else unit_ids = catx('-',unit_ids,unit_id); end; drop unit_id; run; Hope this is helpful, Dan Daniel Nordlund Bothell, WA USA

Thank you for your help with this. Mike On Tue, 12 Jan 2010 01:07:47 -0500, S=?ISO-8859-1?Q?=C3=B8ren?= Lassen <s.lassen@POST.TELE.DK> wrote: >Mike, > >Here is a solution that concatenates all the IDs (as stated), >in a single data step: > >data want; > do _N_=1 by 5; > set have; > by person_id sales_date; > length unit_ids $200; > substr(unit_ids,_n_,4)=put(unit_id,4.0); > if last.sales_date then leave; > substr(unit_ids,_n_+4,1)='-'; > end; > drop unit_id; >run; > >However, if you only have two UNIT_IDs for each SALES_DATE, or if >you only want the range (first and last unit_id) then this will do: > >data want2; > set have; > by person_id sales_date; > retain unit_range ' - '; > if first.sales_date then > substr(unit_range,1,4)=put(unit_id,4.0); > if last.sales_date; > substr(unit_range,6)=put(unit_id,4.0); > drop unit_id; >run; > >NB: Both solutions are assuming that your UNIT_IDs have 4 digits. > >Regards, >Søren > >On Mon, 11 Jan 2010 15:28:05 -0500, Mike Green <mike_green123@YAHOO.COM> >wrote: > >>Hi, >> >>I would appreciate it if someone could help me with problem. >> >>I need to add a variable to the dataset that is a concatenation of the >>UNIT_ID variable by SALES_DATE. Example - for 01SEP06 I need a new unique >>id of 2100-3557. >> >> Obs PERSON_ID SALES_DATE UNIT_ID >> >> 137 77 01SEP06 2100 >> 138 77 01SEP06 3557 >> 139 77 05NOV06 1454 >> 140 77 05NOV06 3765 >> 141 77 13NOV06 1345 >> 142 77 13NOV06 1456 >> >>Thanks, >> >>Mike

Similiar Articles:

Concatenating variable across observations - comp.soft-sys.sas ...
Hi, I would appreciate it if someone could help me with problem. I need to add a variable to the dataset that is a concatenation of the UNIT_ID...

Re: Concatenating Times - comp.soft-sys.sas
Concatenating variable across observations - comp.soft-sys.sas ..... January 11, 2010 1:09 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: Concatenating ...

Reading in BibTeX files using SAS - comp.soft-sys.sas
... each line) over several lines, then concatenate ... the need to read in a single field across observations. ... sys.sas BibTeX is simply a ... ... variable across observations ...

Concatenating variable across observations - sas - Mofeel Groups
sas, Concatenating variable across observations . comp.soft-sys.sas - The SAS statistics package.

7/24/2012 6:32:36 PM