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.
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.