topic copy each row to the row above it they have same ID in SAS Programming

condense data in multiple id to 1 id row

HEB1 — Thu, 23 Mar 2023 10:21:32 GMT

my data looks like this:

data have; input id $ year variable1 variable2 variable3 variable4 variable5 variable6 variable7

x 1999 0 1 0 1 0 0
x 2005 1 1 1 1 1 1
x 2006 0 1 0 1 0 0
y 1989 1 1 1 1 1 1
y 2010 0 1 0 1 0 0

I want to condense all IDs to 1 row so my data will look like this:

id year variable 2 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 7 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 2 variable 3 variable 4 variable 5 variable 6 variable 7
id year variable 2 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 7 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 2 variable 3 variable 4 variable 5 variable 6 variable 7
x 1999 0 1 0 1 0 0 2005 1 1 1 1 1 1 2006 0 1 0 1 0 0
y 1989 1 1 1 1 1 1 2010 0 1 0 1 0 0
z 2000 1 1 1 1 1 1 2003 0 1 0 1 0 0 2004 1 1 1 1 1 1

Thanks!

Re: condense data in multiple id to 1 id row

PaigeMiller — Thu, 23 Mar 2023 10:24:46 GMT

data have; input id $ year variable1 variable2 variable3 variable4 variable5 variable6 variable7

x 1999 0 1 0 1 0 0
x 2005 1 1 1 1 1 1
x 2006 0 1 0 1 0 0
y 1989 1 1 1 1 1 1
y 2010 0 1 0 1 0 0

This is not working SAS code. Could you please fix it (and also please test your code before you provide it). Thanks!

Re: condense data in multiple id to 1 id row

HEB1 — Thu, 23 Mar 2023 11:38:04 GMT

data regd.have; input id $ year $ variable1 $ variable2 $ variable3 $ variable4 $ variable5 $ variable6 $ variable7;datalines;

x 1999 0 1 0 1 0 0 0
x 2005 1 1 1 1 1 1 0
x 2006 0 1 0 1 0 0 0
y 1989 1 1 1 1 1 1 0
y 2010 0 1 0 1 0 0 0
;

here is the correct code for the data

Re: condense data in multiple id to 1 id row

HEB1 — Thu, 23 Mar 2023 12:10:00 GMT

data regd.have; input id $ year $ variable1 $ variable2 $ variable3 $ variable4 $ variable5 $ variable6 $ variable7;datalines;

x 1999 0 1 0 1 0 0 0
x 2005 1 1 1 1 1 1 0
x 2006 0 1 0 1 0 0 0
y 1989 1 1 1 1 1 1 0
y 2010 0 1 0 1 0 0 0
;

I want to condense all IDs to 1 row so my data will look like this:

id year variable 2 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 7 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 2 variable 3 variable 4 variable 5 variable 6 variable 7
id year variable 2 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 7 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 2 variable 3 variable 4 variable 5 variable 6 variable 7
x 1999 0 1 0 1 0 0 2005 1 1 1 1 1 1 2006 0 1 0 1 0 0
y 1989 1 1 1 1 1 1 2010 0 1 0 1 0 0
z 2000 1 1 1 1 1 1 2003 0 1 0 1 0 0 2004 1 1 1 1 1 1

Re: condense data in multiple id to 1 id row

Ksharp — Thu, 23 Mar 2023 12:20:30 GMT

data have; 
input id $ year $ variable1 $ variable2 $ variable3 $ variable4 $ variable5 $ variable6 $ variable7;
datalines;
x 1999 0 1 0 1 0 0 0
x 2005 1 1 1 1 1 1 0
x 2006 0 1 0 1 0 0 0
y 1989 1 1 1 1 1 1 0
y 2010 0 1 0 1 0 0 0
;
data temp;
 set have;
 by id;
 if first.id then n=0;
 n+1;
run;
proc sql noprint;
select distinct catt('temp(where=(n=',n,') rename=(year=year',n,' variable1-variable7=variable',n,'_1-variable',n,'_7))')
 into : merge separated by ' ' 
 from temp;
quit;
data want;
 merge &merge.;
 by id;
 drop n;
run;

Re: condense data in multiple id to 1 id row

HEB1 — Thu, 23 Mar 2023 12:35:08 GMT

Hi,
I don't understand this row:
rename=(year=year',n,' variable1-variable7=variable',n,'_1-variable',n,'_7))')
do I need to write over there the right names of each of the variables?

Re: condense data in multiple id to 1 id row

Kurt_Bremser — Thu, 23 Mar 2023 13:07:50 GMT

A dataset structure like this is not possible, as you cannot have multiple variables with the same name.

Also, such a wide structure is VERY hard to handle in future code; it only makes sense if this is to be a report for human consumption.

So, shall this be a report?

copy each row to the row above it they have same ID

HEB1 — Fri, 24 Mar 2023 06:57:32 GMT

Hi, I have this data

data regd.have; input id $ year $ FD $ REPORTING $ variable3 $ variable4 $ variable5 $ variable6 $ variable7;datalines;

x 1999 0 1 0 1 0 0 0
x 2005 1 1 1 1 1 1 0
x 2006 0 1 0 1 0 0 0
y 1989 1 1 1 1 1 1 0
y 2010 0 1 0 1 0 0 0
;

I want to condense all IDs to 1 row so my data will look like this:, Meaning i want to copy each row to the row above - if it has same cik.

id year variable 2 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 7 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 2 variable 3 variable 4 variable 5 variable 6 variable 7
id year variable 2 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 7 variable 3 variable 4 variable 5 variable 6 variable 8 year variable 2 variable 3 variable 4 variable 5 variable 6 variable 7
x 1999 0 1 0 1 0 0 2005 1 1 1 1 1 1 2006 0 1 0 1 0 0
y 1989 1 1 1 1 1 1 2010 0 1 0 1 0 0
z 2000 1 1 1 1 1 1 2003 0 1 0 1 0 0 2004 1 1 1 1 1 1

Re: copy each row to the row above it they have same ID

Kurt_Bremser — Fri, 24 Mar 2023 07:29:07 GMT

Please do not start new threads for the same question.

Re: condense data in multiple id to 1 id row

Ksharp — Fri, 24 Mar 2023 11:58:10 GMT

Yes. you need to write right variable names if your variable names are different with variable1 variable2.....

My macro variable contains the following code :

temp(where=(n=1) rename=(year=year1 variable1-variable7=variable1_1-variable1_7)) 
temp(where=(n=2) rename=(year=year2 variable1-variable7=variable2_1-variable2_7)) 
temp(where=(n=3) rename=(year=year3 variable1-variable7=variable3_1-variable3_7))