Below is my dataset
In the first row, if some column is blank, then to polulate that column with second row data;
simimally, for third row, if some column is blank then to polulate the column with the fourth row data.
the final dataset looks like this:
How can I write the code to get dataset B? Thanks.
The below code can get first dataset data;
/*Code for first dataset data*/
data a;
format patid event1 event2 event3 event4 event5 event6 event7 column8 column9 $8.;
infile datalines missover dsd ;
input patid event1 event2 event3 event4 event5 event6 event7 column8 column9;
datalines;
001, aa1,, aa3, aa4,, aa6, aa7,, aa9
001, bb1, bb2, bb3,,bb5, bb6, bb7, bb8,,
002,cc1,cc2,cc3,,cc5,cc6,,cc8,cc9
002, dd1,dd2,dd3,dd4,dd5,dd6,dd7,dd8,dd9
;
Here is one way:
data want (drop=_:); merge have have(firstobs=2 drop=patid rename=(event1-event7=_e1-_e7 column8-column9=_c8-_c9)); array now(*) $ event1-event7 column8 column9; array next(*) $ _e1-_e7 _c8 _c9; do i=1 to dim(now); now(i)=coalescec(now(i),next(i)); end; run;
Art, CEO, AnalystFinder.com
Thanks. Your method is novel. The code is short, but learn a lot.
I transposed the datasets first, and dealt with the data, then transposed back.
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.