topic Re: convert observations in to 1 row in SAS Programming

convert observations in to 1 row

sahoositaram555 — Thu, 10 Jun 2021 08:47:12 GMT

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.

Re: convert observations in to 1 row

andreas_lds — Thu, 10 Jun 2021 10:41:34 GMT

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;

Re: convert observations in to 1 row

sahoositaram555 — Fri, 11 Jun 2021 05:13:43 GMT

@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.

Re: convert observations in to 1 row

andreas_lds — Fri, 11 Jun 2021 05:25:50 GMT

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.