Help using Base SAS procedures

Proc sql - select group variable with same prefix

Accepted Solution Solved
Reply
Super Contributor
Posts: 374
Accepted Solution

Proc sql - select group variable with same prefix

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


Accepted Solutions
Solution
‎08-25-2017 12:42 PM
Super Contributor
Posts: 374

Re: Proc sql - select group variable with same prefix

[ Edited ]

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

 

View solution in original post


All Replies
Super User
Posts: 23,776

Re: Proc sql - select group variable with same prefix

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;
Super User
Super User
Posts: 8,127

Re: Proc sql - select group variable with same prefix

[ Edited ]

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;

 

 

Solution
‎08-25-2017 12:42 PM
Super Contributor
Posts: 374

Re: Proc sql - select group variable with same prefix

[ Edited ]

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

 

Super User
Posts: 10,787

Re: Proc sql - select group variable with same prefix

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


Super Contributor
Posts: 374

Re: Proc sql - select group variable with same prefix

I have to work with my mentality. Every time when someone says macro I get freaked out :-)
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2468 views
  • 0 likes
  • 4 in conversation