data table1;
input Form_id question;
cards;
99 1
99 2
99 3
99 4
;run;
data table2;
input question response$ respondent_id;
cards;
2 Apple 1001
3 Car 1001
4 Sky 1001
1 Outside 1002
2 Pear 1002
3 Truck 1002
4 Outside 1002
;run;
proc sql;
create table respondent_id as
select distinct respondent_id
from table2;
data fill (drop=i);
set respondent_id;
do I=1 to 4;
question=I;
output;
end;
run;
proc sql;
create table full_table2 as
select COALESCE(A.respondent_id,B.respondent_id) as respondent_id,
COALESCE(A.question,B.question) as question,
A.response
from WORK.table2 A
full join WORK.fill B
on A.respondent_id=B.respondent_id
and A.question=B.question;
quit;
data dat1; input form_id question; cards; 99 1 99 2 99 3 99 4 ; data dat2; input question response $ respondent_id; cards; 2 Apple 1001 3 Car 1001 4 Sky 1001 1 Outside 1002 2 Pear 1002 3 Truck 1002 4 Outside 1002 ; proc sort data=dat1; by question; run; proc sort data=dat2 nodupkey; by question; run; data want; merge dat1(in=a) dat2(in=b); by question; if a; if question=1 then call missing(response,respondent_id); run;
Sure, give some example data that illustrates some records that have desired results and some that do not.
I don't know what ALL form ids or questions means. I need a list of how many there are and what they are.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: