convert observations in to 1 row

[sahoositaram555]

Hi all,

I have a dataset as below(sample provided).

data abc;
variable="COVAL1";page=3;output;
variable="COVAL1";page=4;output;
variable="COVAL1";page=5;output;
variable="COVAL1";page=6;output;
variable="COVAL2";page=13;output;
variable="COVAL2";page=14;output;
variable="COVAL2";page=15;output;
variable="COVAL2";page=16;output;
run;

I want to generate a new dataset where the result should appear as below.

coval1  3,4,5,6 

coval2  13,14 ,15,16  

The goal is to filter observations from the abc dataset where for similar observations in multiple rows should be filtered and then convert the structure in to a way such as for 1 row per variable name must be created with page numbers  must appear in 1 row with all the pages.

Please guide us how to proceed for a solution for the same.

[andreas_lds · Accepted solution]

I don't see the need to merge data into a list that will be hardly useful, but here you go

data want;
   set work.abc;
   by variable /* notsorted */;
   
   length pageList $ 100;
   retain pageList;
   
   if first.variable then pageList = ' ';
   
   pageList = catx(',', pageList, page);
   
   if last.variable then output;
   
   drop page;
run;

[sahoositaram555]

@andreas_lds thanks, would like to understand 7th line from your code with reference to the previous line. how in the code pageList is able to concatenate the 1st row PAGE value with other rows when we've made the first.pageList variable=missing (in the 6th line of your code). To me, I used to think 7th line will work when we place the 7th line with a then do;end; statement.

Thanks & your comment will be highly appreciated.

[andreas_lds]

To bad that the community software does not put line numbers to code-boxes ...

The retain-statement prevents "pageList" to be automatically set to missing when the pdv is populated during set-statement. Whenever a new value of "variable" is encountered, the value of "pageList" needs to be reset to missing.