topic Listing variables with same prefix in proc sql join in New SAS User

Listing variables with same prefix in proc sql join

accintron — Tue, 13 Aug 2019 17:58:42 GMT

I'm trying to clean up my code and wondering if there is a simpler way to list the REFERER_SOURCE variables instead of listing them all out since they have the same prefix? I know you can reference variables like REFERER_SOURCE_1-REFERER_SOURCE_7 in other commands, but struggling to figure this out with the left join. THanks!

proc sql;
create table adms_4 as
select a.*, b.REFERER_SOURCE_1, b.REFERER_SOURCE_2, b.REFERER_SOURCE_3, b.REFERER_SOURCE_4, b.REFERER_SOURCE_5, b.REFERER_SOURCE_6, b.REFERER_SOURCE_7
from adms_3 a left join RS2 b on a.ADMISSION_ID = b.ADMISSION_ID;
quit;

Re: Listing variables with same prefix in proc sql join

PGStats — Tue, 13 Aug 2019 18:19:18 GMT

SAS/SQL syntax doesn't provide variable list shortcuts. But dataset options do. In some circumstances you can request all variables

... select b.*, ...

and exclude unwanted columns using dataset option drop on input

... from myData(drop=junk1) as b ...

or on output

create table want(drop=junk10) as ...

or include only wanted columns with dataset option keep

... left join RS2(keep=ADMISSION_ID REFERER_SOURCE_: )

Re: Listing variables with same prefix in proc sql join

PaigeMiller — Tue, 13 Aug 2019 18:22:04 GMT

Or do a DATA step merge, where you can abbreviate the list of variables desired to REFERER_SOURCE_:

Re: Listing variables with same prefix in proc sql join

Tom — Tue, 13 Aug 2019 18:55:55 GMT

Are you doing a many-to-many merge?

If not then just use normal SAS code instead of SQL and then you can use variable lists in many places.

For this example you can probably just use a KEEP= option on the RS2 input dataset in either SQL or normal code.

data adms_4;
  merge   adms_3(in=in_a) RS2 (keep=admission_id referer_source_: );
  by ADMISSION_ID;
  if in_a;
run;

In SQL you might want to also rename the duplicate ADMISSION_ID column so that you can use * in your column list without getting warnings/notes about duplicate names. You can then DROP it from the output dataset.

create table adms_4(drop=id_B) as
  select * 
  from adms_3 a 
  left join RS2(keep=admission_id referer_source_: rename=(admission_id=id_B)) b 
  on a.ADMISSION_ID = b.id_B
;

Re: Listing variables with same prefix in proc sql join

accintron — Wed, 14 Aug 2019 14:20:40 GMT

Ah, thank you! Not doing many to many merge. These suggestions are so helpful and straightforward . I appreciate it!