The simplest method would be to use autoupdate :
data datawant;
update datahave(obs=0) datahave;
by id;
run;
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.
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.
The simplest method would be to use autoupdate :
data datawant;
update datahave(obs=0) datahave;
by id;
run;
It works perfectly as I wanted. Thank you so much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.