BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ZoeyElle
Calcite | Level 5

Good day, everyone.

Here's my sample data:

Key.            Data

Abc123.      077123

Abc123.      098546-

Abc123.      875433

Def123.       345423

Def123.       344565x

I need to get result as follows:

Abc123.       077123

                     098546-

                     875433

Def123.        345423

                     344565x

The result should only be 2 observations. 1st observation is with key Abc123 . 2nd observation is with key Def123.

Notice the data column has been combined into one variable and displayed below one another....but in a single observstion.

How can i do the above. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
kmw
SAS Employee kmw
SAS Employee

If you want to collapse observations of the same BY value into a single observation, you can use the sample code in this SAS note.

 

http://support.sas.com/kb/24/745.html

 

 

 

View solution in original post

14 REPLIES 14
LinusH
Tourmaline | Level 20

Seems like a report requirement to me...?

Take a look at PROC PRINT with BY and ID statements.

/Linus

Data never sleeps
FriedEgg
SAS Employee

data input;

input key $ data $;

cards;

Abc123.      077123

Abc123.      098546-

Abc123.      875433

Def123.      345423

Def123.      344565x

;

run;

proc sort data=input; by key; run;

/* Option 1 */

proc print data=input noobs; by key; id key; run;

/* Option 2 */

data output;

length all_data $256;

set input;

by key;

retain all_data;

if first.key then all_data=data;

  all_data=catx(' ', all_data, data);

if last.key then output;

run;

proc print data=output; run;

DLing
Obsidian | Level 7

May I humbly suggest this is an ill-posed question, not in the SAS way of looking at the problem.  The notion that "1 field" spans "3 lines" just doesn't exist in SAS.  If it is merely for reporting, then as other posters have suggested, there are multiple ways to have it printed/displayed that way.

MikeZdeb
Rhodochrosite | Level 12

Hi ... here's another idea.  Use PROC SUMMARY to aggregate the values within a vakue of KEY, then use a data step to

concatenate the values into one variable.  The CLASS variable avoids the sort.

data input;

input key $ data $;

cards;

Abc123.      077123

Abc123.      098546-

Abc123.      875433

Def123.      345423

Def123.      344565x

;

run;

proc summary nway data=input missing;

class key;

output out=new (drop=_type_ _freq_) idgroup(out[3](data)=);

run;

data new;

set new;

newvar = catx(' ', of data:);

keep key newvar;

run;

If you are not sure of the number of observations within each key, you can use ...

proc sql noprint;

select max(obs) into :obs

from (select count(*) as obs from input group by key);

quit;

prior to PROC SUMMARY and substitute [&obs] for the [3] used above;

fyi ... "Transposing Data Using PROC SUMMARY'S IDGROUP Option"

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

mkeintz
PROC Star

If you don't know the size of the largest group, you could also just put in a number (say 100) that you know is larger than the size of any possible key group.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Irfanmohammed
Calcite | Level 5

data input;

input key $ data $;

cards;

Abc123.      077123

Abc123.      098546-

Abc123.      875433

Def123.      345423

Def123.      344565x

;

run;

Proc sort data=input nodupkey out=Input1;

by key;

run;

this will delet the number of dulicate observation.

mkeintz
PROC Star

But it will also lose the data components from the 2nd, 3rd, etc records for each key.  The OP wants to keep them.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Irfanmohammed
Calcite | Level 5

if this the case then you will have to creat a new data set by copying this data set and give an option of first.(variable) then output=(new variable); run;

Peter_C
Rhodochrosite | Level 12

assuming your data are in KEY order, you could write this short data step

data out1 ( compress= yes  keep= key collection ) ;
   retain KEY ;
   length collection $10000 ; 
   Do item=1 by 1 until( last.KEY ) ;
      set your_data ;
      by KEY ;
      collection = catX( ',', collection, data ) ;
   End ;   
run ;



That seems likely to provide what is wanted but without arranging the comma-separated data in COLLECTION into a column

Perhaps the separator (in the catx() function call) should be '0A'x instead of ','.
Then in excel the collection would / might appear on multiple lines within the cell.

Peter_C
Rhodochrosite | Level 12
edited to format code and remove typo(s)
kmw
SAS Employee kmw
SAS Employee

If you want to collapse observations of the same BY value into a single observation, you can use the sample code in this SAS note.

 

http://support.sas.com/kb/24/745.html

 

 

 

jsbyxws
Obsidian | Level 7

Hi Peter,

 

Thanks for the code! It works and I don't know why. Can you help to explain step by step for your code with following sample data (name have😞

Key Data

A      A1

B     B1

B     B2

 

 

The result based on your code would be:

Key Collection Item (I keep it since it is count)

A     A1             1

B    B1,B2         2

 

But I am still confused by your "Set Your_data" inside do loop and other steps. Can you show us how observation 1, 2, 3 work in detail?

 

 

Peter_C
Rhodochrosite | Level 12
Try the dataset debugger (which now works in EG ).
It allows you to watch values change, as each statement is executed
Peter_C
Rhodochrosite | Level 12
Apple. for typo.... look for the data step debugger
There are a number of great articles in the SAS DUMMY blog.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 37044 views
  • 0 likes
  • 10 in conversation