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!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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