DATA Step, Macro, Functions and more

Moving values row up

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Moving values row up

[ Edited ]
 
Dear colleagues,

I need to transform a table so I have one row by ID variable. My present database is like that:
 
ID X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 Xn
1 90 . . . . . . . . . .
1 . 91 . . . . . . . . .
1 . . 03 . . . . . . . .
2 98 . . . . . . . . . .
3 94 . . . . . . . . . .
3 . 95 . . . . . . . . .
3 . . 00 . . . . . . . .
3 . . . 00 . . . . . . .
4 07 . . . . . . . . . .
4 . 08 . . . . . . . . .
4 . . 08 . . . . . . . .
4 . . . 08 . . . . . . .
4 . . . . 10 . . . . . .
 
 
 
My highest value "n" ends in 20 for Xn. Some individuals will face just 1 event, others will face 20 events. ID has more than 10,000 values. X is the year of event variable.
As you can see for ID 3 and 4, individuals can live two different events at the same year.
 
I want to create this table:
ID X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 Xn
1 90  91  03 . . . . . . . .
2 98 . . . . . . . . . .
3 94 95 00 00 . . . . . . .
4 07 08 08 08 10 . . . . . . 
 
I want to create a one row by individual. I have searched for this information in the forums, but I did not find anything. Can anybody help me? Many thanks in advance.

Accepted Solutions
Solution
‎03-14-2018 12:00 PM
Esteemed Advisor
Posts: 5,616

Re: Moving values row up

Posted in reply to quasenada

The simplest method would be to use autoupdate :

 

data datawant;
update datahave(obs=0) datahave;
by id;
run;
PG

View solution in original post


All Replies
Super User
Posts: 13,889

Re: Moving values row up

Posted in reply to quasenada

The way you posted the data is very hard to read and the result is not very clear.

 

The best way to post data is to use a data step so others have data to actually manipulate. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

Best is to actually post the result for a given example data set.

I am not sure if you want a data set or report.

 

 

New Contributor
Posts: 3

Re: Moving values row up

[ Edited ]

Dear ballardw, thanks for you help. Sorry for the way I posted my question, this was my first time.

 

So, I now post again giving a example of how my data is and how I want to reshape it. Attached to this post you find a "datahave" file that represents how my data is organized and another "datawant" that represents how I want them to be. You can use the following program to import the data. My present database is like that:

 

/* Format that I have*/

data have;
infile "PUT HERE datahave FILE LOCATION IN YOUR COMPUTER, INCLUDIND .txt" 
LRECL=1000 DLM=' ' firstobs=2;
input
ID X1 X2 X3 X4 X5 X6 X7 X8 X9 Xn
;
run;

 

 

I need to transform in a table of one row by ID variable. My desired database is like that:

 

/* Format that I want*/

data want;
infile "PUT HERE datawant FILE LOCATION, INCLUDIND .txt"
LRECL=1000 DLM=' ' firstobs=2;
input
ID X1 X2 X3 X4 X5 X6 X7 X8 X9 Xn
;
run;

 

Some individuals will face just the first event and I need to code the other events' variables as missing values, other individuals will face 20 events. ID has more than 10,000 values (the database is huge). X is the year of the event variable.

As you can see for ID 3 and 4, individuals can live two different events at the same year.
 
I want to create a one row by individual table. I have searched for this information in the forums, but I did not find anything. I tried using the following proc transpose, but the results are not what I want.
 
proc transpose data=have out=wideformat;
by ID;
var X1--Xn;
run;
 
 
Can anybody help me? Again, many thanks in advance.
Solution
‎03-14-2018 12:00 PM
Esteemed Advisor
Posts: 5,616

Re: Moving values row up

Posted in reply to quasenada

The simplest method would be to use autoupdate :

 

data datawant;
update datahave(obs=0) datahave;
by id;
run;
PG
New Contributor
Posts: 3

Re: Moving values row up

It works perfectly as I wanted. Thank you so much!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 112 views
  • 0 likes
  • 3 in conversation