Proc sql - select group variable with same prefix

[Cruise]

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

[Reeza]

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;

[Tom]

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;

[Cruise · Accepted solution]

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

[Ksharp]

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

[Cruise]

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