topic Re: Proc sql - select group variable with same prefix in SAS Procedures

Proc sql - select group variable with same prefix

Cruise — Thu, 24 Aug 2017 22:32:08 GMT

I'd like to select group variable with same prefix Code_1-Code_30 in proc sql preferrentially selecting from "inp" data. It is tedious to spell out all 30 codes down there.

Is there any way to take advantage of the same prefix? in this context?

PROC SQL;
 create table ip_only(keep= var1 Code_:) as 
 SELECT inp.var1 as var1,
		inp.Code_1 as Code_1, 
		inp.Code_2 as Code_2 through Code_30
 FROM uniq_ip inp left join uniq_op outp
 on inp.personal_id = outp.personal_id;
QUIT;

I'm using SAS 9.4.

Thanks

Re: Proc sql - select group variable with same prefix

Reeza — Thu, 24 Aug 2017 22:42:43 GMT

You don't need the AS NEW_VAR_NAME part when it's not changing

inp.var1, inp.code_1

There is no way to short cut lists in SQL. You can use the FEEDBACK option to avoid typing it all out if you'd like.

Run the proc with the FEEDBACK option and * for the INP table that you're interested in. Check the log for the query and copy that to your code window now. Edit as needed.

proc sql feedback;
create ...
select inp.*
from ....

quit;

Re: Proc sql - select group variable with same prefix

Tom — Fri, 25 Aug 2017 13:26:14 GMT

No, SQL syntax does not support SAS variable lists.

But you might be able to use KEEP= and/or DELETE= dataset options so that you can just use * in the SELECT statement.

So say you wanted to keep VAR1 and CODE1 to CODE30, but you also needed to keep PERSONAL_ID for use in the join condition. Your program might end up looking like this.

proc sql;
create table ip_only (drop=personal_id) as
  select inp.*
  from uniq_ip(keep=personal_id var1 code1-code30) inp
  left join uniq_op outp
    on inp.personal_id = outp.personal_id
;
quit;

Re: Proc sql - select group variable with same prefix

Ksharp — Fri, 25 Aug 2017 13:24:58 GMT

As @Tom said before, SQL don't support variable listing method, depress.
But you can make a macro for it.


%macro xxxx;
PROC SQL;
 create table ip_only(keep= var1 Code_:) as 
 SELECT inp.var1 as var1,
    %do i=1 %to 30;
		inp.Code_&i as Code_&i
       %if &i ne 30 %then %do;  ,   %end; 
    %end;
 FROM uniq_ip inp left join uniq_op outp
 on inp.personal_id = outp.personal_id;
QUIT;
%mend;


%xxxx

Re: Proc sql - select group variable with same prefix

Cruise — Fri, 25 Aug 2017 14:34:52 GMT

I have to work with my mentality. Every time when someone says macro I get freaked out 🙂

Re: Proc sql - select group variable with same prefix

Cruise — Fri, 25 Aug 2017 14:38:49 GMT

@Tom

It worked out excellent. However at the price of taking out all following conversions out of sql to do in separate data steps. This is because I had several date variables needed conversion.

select 
 input(inp.birth_date,yymmdd8.) as birth_date,
      case when 
           (inp.Care_Date > outp.Care_Date) then 1
		   when 
		   (inp.Care_Date < outp.Care_Date) then 0
		   else 99
      end as first_op
from...