BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cruise
Ammonite | Level 13

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cruise
Ammonite | Level 13

@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

5 REPLIES 5
Reeza
Super User

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
Super User Tom
Super User

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
Ammonite | Level 13

@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
Super User
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
Ammonite | Level 13
I have to work with my mentality. Every time when someone says macro I get freaked out 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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