Proc SQL case when select

[ddavies]

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. 

[Tom]

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.

[gamotte · Accepted solution]

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;