I am thinking there is a different approach entirely for how to do this...
I know the syntax is incorrect but here is a sample of what I am trying to accomplish:
proc sql; create table final as select people_id, event_name, (case when event_name = "CG Version" then sum(first_cg_scared) as first_cg_scared, sum(last_cg_scared) as last_cg_scared, sum(first_cg_fun) as first_cg_fun, sum(last_cg_fun) as last_cg_fun, end) (case when event_name = "Adult Version" then sum(first_adult_helpless) as first_adult_helpless, sum(last_adult_helpless) as last_adult_helpless, sum(first_adult_worthless) as first_adult_worthless, sum(last_adult_worthless) as last_adult_worthless, end) (case when event_name = "Pediatric Version" then sum(first_ped_felt_scared) as first_ped_felt_scared, sum(last_ped_felt_scared) as last_ped_felt_scared, sum(first_ped_nervous) as first_ped_nervous, sum(last_ped_nervous) as last_ped_nervous end) from first_last_d group by people_id, event_name order by people_id; quit;
Essentially, I have 3 versions of an assessment. If the event_name is the CG version, I want to create a table with the one set of variables, if it is the Adult version, I want to create a table with those variables, and if it is the pediatric version, I want to use those variables.
Hello,
Can you post an example of have and want datasets ?
Edit: removed useless test from previous attempt.
data variables;
input event_name $20. variable $20.;
cards;
CG Version first_cg_scared
CG Version last_cg_scared
CG Version first_cg_fun
CG Version last_cg_fun
Adult Version first_adult_helpless
Adult Version last_adult_helpless
Adult Version first_adult_worthless
Adult Version last_adult_worthless
Pediatric Version first_ped_felt_scared
Pediatric Version last_ped_felt_scared
Pediatric Version first_ped_nervous
Pediatric Version last_ped_nervous
;
run;
data first_last_d;
input event_name $20. first_cg_scared last_cg_scared first_cg_fun last_cg_fun;
cards;
CG Version 1 2 3 4
CG Version 5 6 7 8
;
run;
proc sort data=variables;
by event_name;
run;
data _NULL_;
merge first_last_d (obs=1 keep=event_name in=infld) variables;
by event_name;
if infld then do;
if first.event_name then call execute(cat('proc sql; create table final as select sum(',variable,') as ', variable));
else call execute(cat(', sum(',variable,') as ', variable));
if last.event_name then call execute('from first_last_d; quit;');
end;
run;
A single query in PROC SQL can only create a single table. But I don't think you want to create multiple tables.
Looks like you want to put the CASE inside of the SUM() aggregate function.
Do you really want to include EVENT_NAME in the list of variables and in the GROUP_BY? That will make a checker board output.
Here I have done it for the first value of EVENT_NAME. Just replicate the pattern for the others.
proc sql;
create table final as
select people_id
, sum(case when event_name = "CG Version" then first_cg_scared end) as first_cg_scared
, sum(case when event_name = "CG Version" then last_cg_scared end) as last_cg_scared
, sum(case when event_name = "CG Version" then first_cg_fun end) as first_cg_fun
, sum(case when event_name = "CG Version" then last_cg_fun end) as last_cg_fun
, ...
from first_last_d
group by people_id
order by people_id
;
quit;
What values to the event specific variables have on the observations that are not for those events?
If they are missing (or zero in case of SUM() function) then you can just skip the CASE statement and sum the variables directly.
Note it will probably be much easier to do this with PROC SUMMARY instead of this verbose SQL syntax.
Hello,
Can you post an example of have and want datasets ?
Edit: removed useless test from previous attempt.
data variables;
input event_name $20. variable $20.;
cards;
CG Version first_cg_scared
CG Version last_cg_scared
CG Version first_cg_fun
CG Version last_cg_fun
Adult Version first_adult_helpless
Adult Version last_adult_helpless
Adult Version first_adult_worthless
Adult Version last_adult_worthless
Pediatric Version first_ped_felt_scared
Pediatric Version last_ped_felt_scared
Pediatric Version first_ped_nervous
Pediatric Version last_ped_nervous
;
run;
data first_last_d;
input event_name $20. first_cg_scared last_cg_scared first_cg_fun last_cg_fun;
cards;
CG Version 1 2 3 4
CG Version 5 6 7 8
;
run;
proc sort data=variables;
by event_name;
run;
data _NULL_;
merge first_last_d (obs=1 keep=event_name in=infld) variables;
by event_name;
if infld then do;
if first.event_name then call execute(cat('proc sql; create table final as select sum(',variable,') as ', variable));
else call execute(cat(', sum(',variable,') as ', variable));
if last.event_name then call execute('from first_last_d; quit;');
end;
run;
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.