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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.