Moving values row up

[quasenada]

 

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.

[ballardw]

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.

[quasenada]

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.

[PGStats · Accepted solution]

The simplest method would be to use autoupdate :

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

[quasenada]

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