topic Re: Concatenate SAS data rows ? in SAS Procedures

Concatenate SAS data rows ?

KevinC_ — Wed, 25 Nov 2009 15:34:43 GMT

cell_code dropcode mail_date seed
----------------------------------------------------------------------
NR800 1 12/07/2009 N
NR800 1 12/07/2009 Y
NR801 1 12/07/2009 N
NR801 1 12/07/2009 Y
NR801 2 12/07/2009 N

Hello everyone,

I have posted the first few rows of a sas dataset. Is there a way I can 'concatenate' rows based on the cell_code?
For example, the first two rows have the same cell_code so the output would look like the following:

NR800 1 12/07/2009 N NR800 1 12/07/2009 Y

Thank you so much for any input !

Kevin

Re: Concatenate SAS data rows ?

KevinC_ — Wed, 25 Nov 2009 15:37:43 GMT

Let me be more clear.. The first two rows before concatenation:

NR800 1 12/07/2009 N
NR800 1 12/07/2009 Y

After:

NR800 1 12/07/2009 N NR800 1 12/07/2009 Y

Thanks.

Re: Concatenate SAS data rows ?

Doc_Duke — Wed, 25 Nov 2009 15:46:44 GMT

PROC TRANSPOSE will do approximately that (it won't repeat the cell code).

If you want to concatenate the raw data that way, you can do it in the DATA step. Look at the @@ notation for the PUT statement and add something like

FILE myoutputfile; *that you have defined;
IF lag(cell_code) = cell_code THEN PUT _infile_ @@;
ELSE PUT;

Doc Muhlbaier
Duke

Re: Concatenate SAS data rows ?

abdullala — Wed, 25 Nov 2009 16:17:04 GMT

this is a quite unique request that all fields need to be replicated. I can not think of any one-step way to achieve that.

1. sort the data and assign a unique ID to each records within a cell_code.
2. output to 2 separate datasets with this ID and change variable names.
3. merge the 2 new datasets using proc sql, with value of original cell_code.

or if you need to really 'concatenate' those fields, you may first concatenate all 4 fields first and then do transpose to this one concatenated field. if this is what you wanted then it's an easier way out.

Re: Concatenate SAS data rows ?

Flip — Wed, 25 Nov 2009 17:08:35 GMT

I think before you can get an exact answer we would need to know a few things. Are you creating a new data set, or producing output?
Is there a limit on how many rows you might have to transpose?

Re: Concatenate SAS data rows ?

Cynthia_sas — Wed, 25 Nov 2009 17:10:31 GMT

Hi:
I'm curious how you'd envision the new variables being named???
You currently have this:
[pre]
cell_code dropcode mail_date seed
NR800 1 12/07/2009 N
NR800 1 12/07/2009 Y
[/pre]

what would the new variables be named???
[pre]
cell_code dropcode mail_date seed cell2 drop2 mail2 seed2
NR800 1 12/07/2009 N NR800 1 12/07/2009 Y
[/pre]

I'm curious also about why you want cell_code repeated, since that is the value you are using to concatenate???

cynthia

Re: Concatenate SAS data rows ?

KevinC_ — Wed, 25 Nov 2009 18:52:21 GMT

Thank you everyone for your input.

Cynthia,
Actually I dont need the cell_code repeated. This is what I need in the new dataset:

cell_code, dropcode, mail_date, seed

sorry about the confusion.

Thanks again.

Re: Concatenate SAS data rows ?

KevinC_ — Wed, 25 Nov 2009 18:53:22 GMT

oops.. let me correct myself...

This is what I need in the new dataset:

cell_code, dropcode, mail_date, seed1, seed2

Re: Concatenate SAS data rows ?

KevinC_ — Wed, 25 Nov 2009 18:56:15 GMT

Before:

NR800 1 12/07/2009 N
NR800 1 12/07/2009 Y

After:

NR800 1 12/07/2009 N Y

Re: Concatenate SAS data rows ?

sbb — Wed, 25 Nov 2009 18:59:32 GMT

As was communicated previously, use PROC TRANSPOSE. Suggest you review the SAS DOC on this procedure and also consider searching the SAS support http://support.sas.com/ website for supplemental technical reference material on the topic.

Scott Barry
SBBWorks, Inc.

Re: Concatenate SAS data rows ?

Cynthia_sas — Wed, 25 Nov 2009 20:02:44 GMT

Hi:
You say that you only want:
cell_code, dropcode, mail_date, seed1 and seed2... but what about the NR801 cell_code???? It has dropcode of 1 and dropcode of 2?? I thought the only determinant for concatenation was cell_code.
[pre]
cell_code dropcode mail_date seed
NR801 1 12/07/2009 N
NR801 1 12/07/2009 Y
NR801 2 12/07/2009 N
[/pre]

How would you envision those records being concatenated????
[pre]
cell_code drop1 drop2 drop3 mail1 mail2 mail3 seed1 seed2 seed3
NR800 1 1 . 12/07/2009 12/07/2009 . N Y
NR801 1 1 2 12/07/2009 12/07/2009 12/07/2009 N Y N
[/pre]

Is it possible for there to be differing mail_date values as well???? Remember that EVERY row in your final dataset will have the same number of COLUMNS -- you can't have 1 row/observation with only 1 dropcode and another row/observation with 3 dropcodes.

cynthia

Re: Concatenate SAS data rows ?

KevinC_ — Fri, 27 Nov 2009 14:47:04 GMT

Hi Cynthia,

Thanks for your input. Yes you are correct: there could be different dropcode and maildate as well. Here is a section of data that has different dropcode and mail_date and the proc transpose I have put together. The rresult is close to what i want but not quite.

I can't get the 'seed' to show (either Y or N). in other words, in the first line of the output i would like to see 'N' next to the 421 and 'Y' next to the 2. I tried adding ID=seed and it didn't work. Do you have any suggestions?

proc transpose data=freq_file1 out=kyc;
by cell_code dropcode mail_date;
var count;
run;

INPUT:
cell_code dropcode mail_date seed count cumulative_ frequency
NR807 6 12/08/2009 N 421 3032
NR807 6 12/08/2009 Y 2 3034
NR807 7 12/09/2009 N 500 3534
NR807 8 12/09/2009 N 500 4034
NR807 9 12/09/2009 N 500 4534
NR807 10 12/10/2009 N 500 5034
NR807 11 12/10/2009 N 500 5534
NR807 12 12/10/2009 N 77 5611

OUTPUT:
13 NR807 6 18239 COUNT Frequency Count 421 2
14 NR807 7 18240 COUNT Frequency Count 500
15 NR807 8 18240 COUNT Frequency Count 500
16 NR807 9 18240 COUNT Frequency Count 500
17 NR807 10 18241 COUNT Frequency Count 500
18 NR807 11 18241 COUNT Frequency Count 500
19 NR807 12 18241 COUNT Frequency Count 77

Re: Concatenate SAS data rows ?

Flip — Fri, 27 Nov 2009 15:51:17 GMT

I see a couple of ways to handle this.

data one;
input
cell_code $5. dropcode mail_date mmddyy10. +1 seed $1. count cumulative_frequency ;
cards;
NR807 6 12/08/2009 N 421 3032
NR807 6 12/08/2009 Y 2 3034
NR807 7 12/09/2009 N 500 3534
NR807 8 12/09/2009 N 500 4034
NR807 9 12/09/2009 N 500 4534
NR807 10 12/10/2009 N 500 5034
NR807 11 12/10/2009 N 500 5534
NR807 12 12/10/2009 N 77 5611
run;

proc sort data = one out = two;
by cell_code dropcode mail_date seed;
run;

/* Option 1 */

data output(keep = cell_code dropcode mail_date nseed1 - nseed2 ncount1-ncount2 cumulative_frequency);
set two;
format mail_date date9.;
by cell_code dropcode mail_date seed;
array nseed(2) $;
array ncount(2);
retain nseed1 - nseed2 ncount1-ncount2;
if first.mail_date then do;
do i = 1 to 2;
nseed(i) = '';
ncount(i) = .;
end;
i = 1 ;
end;
nseed(i) = seed;
ncount(i) = count;
if last.mail_date then output;
I + 1;
run;

/* Option 2 */

data three;
set two;
by cell_code dropcode mail_date seed;
if first.dropcode then i = 0;
i+1;
run;

proc transpose data = three out = seeds prefix = seed;
by cell_code dropcode mail_date ;
var seed ;
id i;
run;

proc transpose data = three out = counts prefix = count;
by cell_code dropcode mail_date ;
var count;
id i;
run;

data output2;
merge seeds counts;
format mail_date date9.;
by cell_code dropcode mail_date ;
run;

Re: Concatenate SAS data rows ?

KevinC_ — Mon, 30 Nov 2009 16:44:56 GMT

Thank you, Flip!
Thanks to everyone else too!

Flip, one last question: in Option 2, what's the purpose of "i"?
"if first.dropcode then i = 0;"
"id i"

Re: Concatenate SAS data rows ?

Flip — Mon, 30 Nov 2009 17:55:26 GMT

It gives you the number for the column name ie. prefix-i for column seed1 seed2

Re: Concatenate SAS data rows ?

MikeZdeb — Wed, 29 Jun 2011 19:14:47 GMT

Hi ... this is a really old post (2009), but it's a chance to learn something new.

You can transpose data with PROC SUMMARY. See the paper ...

Transposing Data Using PROC SUMMARY'S IDGROUP Option

John King, Ouachita Clinical Data Services, Mount Ida, AR

Mike Zdeb, U@Albany School of Public Health, Rensselaer, NY

http://www.lexjansen.com/pharmasug/2009/tt/tt08.pdf

For the posted data ...

data one;

input

cell_code: $5.

dropcode

mail_date : mmddyy.

seed : $1.

;

format mail_date mmddyy10.;

datalines;

NR800 1 12/07/2009 N

NR800 1 12/07/2009 Y

NR801 1 12/07/2009 N

NR801 1 12/07/2009 Y

NR801 2 12/07/2009 N

;

run;

* find the maximum number of observations within a CELL_CODE;

proc sql noprint;

select max(obs) into :obs

from

(select count(*) as obs

from one

group by cell_code)

;

quit;

* use PROC SUMMARY to transpose the data (use the macro variable from PROC SQL) ... SAS names the new variables;

proc summary nway data=one missing;

class cell_code;

output out=two (drop=_type_ _freq_) idgroup(out[&obs](dropcode mail_date seed)=);

run;

proc print data=two;

run;

cell_ dropcode_ dropcode_ dropcode_ mail_date_ mail_date_ mail_date_

code 1 2 3 1 2 3 seed_1 seed_2 seed_3

NR800 1 1 . 12/07/2009 12/07/2009 . N Y

NR801 1 1 2 12/07/2009 12/07/2009 12/07/2009 N Y N

Concatenate SAS data rows ?

Ksharp — Thu, 30 Jun 2011 03:51:17 GMT

OK.Let me try it. Since I like data step more,and MikeZdeb 's order of

variable maybe is not what you need.

data temp;
infile datalines dlm=' ';
input cell_code $   dropcode     mail_date : mmddyy10.   seed  $;
format mail_date mmddyy10.;
datalines; 
NR800         1          11/07/2009    N          
NR800         1          12/07/2009    Y            
NR801         1          10/07/2009    N            
NR801         1          11/07/2009    Y             
NR801         2          12/07/2009    N 
;
run;


data _null_;
 set temp end=last;
 by cell_code notsorted;
 if _n_ eq 1 then call execute('data want;');
 if first.cell_code then count=0;
 count+1;
 call execute(cats('cell_code',count,'="',cell_code,'";') );
 call execute(cats('dropcode',count,'=',dropcode,';')) ;
 call execute(cats('mail_date',count,'=',mail_date,';') );
 call execute(cats('seed',count,'="',seed,'";')) ;
 if last.cell_code then call execute('output;');
 if last then call execute('format mail_date: mmddyy10.;run;');
run;

Ksharp