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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.