BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
quasenada
Calcite | Level 5
 
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.
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

The simplest method would be to use autoupdate :

 

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

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Calcite | Level 5

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
Opal | Level 21

The simplest method would be to use autoupdate :

 

data datawant;
update datahave(obs=0) datahave;
by id;
run;
PG
quasenada
Calcite | Level 5

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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