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