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

Hi,

I have a specific project which needs a lot of union sqls and most field in the sqls are the same,

say,

select t1.a1,t1.a2,t1.a3,t1.a4 from have as t1

union

select t1.a1,t1.a2,t1.a3,t1.a5 from have as t1

union

select t1.a1,t1.a2,t1.a3,t1.a6 from have as t1

because i 'm debuging the program, lots of fields is confusing ,

I want the following

select var1 ,t1.a4 from have as t1

union

select var1,t1.a5 from have as t1

union

select var1,t1.a6 from have as t1

which means use var1 to replace  t1.a1,t1.a2,t1.a3

can it be achieved?

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

I may not have correctly understood what you are looking for, but it sounds like you want to use a macro variable to substitute some text.  e.g.:

%let var1=name,age;

proc sql;

  create table table1 as

    select &var1.,sex

      from sashelp.class

  ;

  create table  table2 as

    select &var1.,weight

      from sashelp.class

  ;

  create table table3 as

    select &var1.,height

      from sashelp.class

  ;

quit;

View solution in original post

5 REPLIES 5
Ksharp
Super User

Make another variable :

data have;

set have;

var1=catx(' ',of a1 - a4);

run;

bbb_NG
Fluorite | Level 6

Ksharp,

Thanks for your reply. but I'm more confused.thus I put the original code about 10%,

I'm wondering whether your method can be applied in the following way?

PROC SQL /*Generate vertical data, duplicated*/;

   CREATE TABLE wutemp.CustDemoHistory AS

/*Age*/  SELECT t1.branch_code,t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP,   t1.nationcmp,  version,t1.'New Cust Ind'n,    t1.OccupationCMP,t1.AGECMP  as cmp,         

            (COUNT(t1.AgeCMP)) as  NoCount,trblevel,zoneCMP,agecmp

      FROM wutemp.CUSTDEMOBASEHistory  AS t1

      GROUP BY t1.branch_code, t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP,   t1.nationcmp,    version, t1.'New Cust Ind'n, t1.OccupationCMP,   trblevel,zoneCMP,agecmp

/*Sex*/union SELECT t1.branch_code,t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP,   t1.nationcmp,version,          t1.'New Cust Ind'n,           t1.OccupationCMP,        

T1.'Sex Code'n as CMP    ,            (COUNT(*))  AS NoCount,trblevel,zoneCMP,agecmp

      FROM wutemp.CUSTDEMOBASEHistory  AS t1

      GROUP BY t1.branch_code, t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP,   t1.nationcmp,    version, t1.'New Cust Ind'n, t1.OccupationCMP,  trblevel  ,  t1.'Sex Code'n,zoneCMP,agecmp

/*TRBCount*/union SELECT t1.branch_code,t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP, t1.nationcmp,  version,          t1.'New Cust Ind'n,

          t1.OccupationCMP,       trblevel as CMP    ,            (COUNT(*))  AS NoCount,trblevel,zoneCMP,agecmp

      FROM wutemp.CUSTDEMOBASEHistory  AS t1

      GROUP BY t1.branch_code, t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP,   t1.nationcmp,    version, t1.'New Cust Ind'n, t1.OccupationCMP,     trblevel,zoneCMP,agecmp

/*Vintage*/union SELECT t1.branch_code,t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP, t1.nationcmp,  version,          t1.'New Cust Ind'n,

          t1.OccupationCMP,         Vintage as CMP    ,            (COUNT(*))AS NoCount,trblevel,zoneCMP,agecmp

      FROM wutemp.CUSTDEMOBASEHistory  AS t1

      GROUP BY t1.branch_code, t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP,   t1.nationcmp,    version, t1.'New Cust Ind'n, t1.OccupationCMP,    trblevel ,  Vintage,zoneCMP,agecmp

/*SATRBCount*/ union SELECT t1.branch_code,t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP,t1.nationcmp, version,          t1.'New Cust Ind'n,

          t1.OccupationCMP,      T1.SAtrblevel as CMP    ,    (COUNT(*))  AS NoCount,trblevel,zoneCMP,agecmp

      FROM wutemp.CUSTDEMOBASEHistory  AS t1

      GROUP BY t1.branch_code, t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP,   t1.nationcmp,    version, t1.'New Cust Ind'n, t1.OccupationCMP, trblevel ,  SAtrblevel,zoneCMP,agecmp

/*TRBSUM*/union SELECT t1.branch_code,t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP, t1.nationcmp,  version,          t1.'New Cust Ind'n,

          t1.OccupationCMP,               (Case

WHEN t1.TRBLevel ='TRB1:0_10K'  THEN 'TRB9_1:0_10K' 

WHEN t1.TRBLevel='TRB2:10k_100K'  THEN 'TRB9_2:10k_100K'

WHEN t1.TRBLevel ='TRB3:100k_300K' THEN 'TRB9_3:100k_300K'

WHEN t1.TRBLevel ='TRB4:300k_1M' THEN 'TRB9_4:300k_1M'

WHEN t1.TRBLevel='TRB5:1M_3M'  THEN 'TRB9_5:1M_3M'

WHEN t1.TRBLevel= 'TRB6:3M_5M'  THEN  'TRB9_6:3M_5M'

WHEN t1.TRBLevel= 'TRB7:Over5M'  THEN 'TRB9_7:Over5M'

else  'TRB9_8:Nill'

End ) as CMP    ,    (sum('Last Month Average Balance'n))  AS NoCount,trblevel,zonecmp,agecmp

      FROM wutemp.CUSTDEMOBASEHistory  AS t1

      GROUP BY t1.branch_code, t1.'PFS Cust Segment Code'n, LoanCMP,OutstandingCMP,   t1.nationcmp,    version, t1.'New Cust Ind'n, t1.OccupationCMP, trblevel ,  SAtrblevel,zoneCMP,agecmp

art297
Opal | Level 21

I may not have correctly understood what you are looking for, but it sounds like you want to use a macro variable to substitute some text.  e.g.:

%let var1=name,age;

proc sql;

  create table table1 as

    select &var1.,sex

      from sashelp.class

  ;

  create table  table2 as

    select &var1.,weight

      from sashelp.class

  ;

  create table table3 as

    select &var1.,height

      from sashelp.class

  ;

quit;

bbb_NG
Fluorite | Level 6

Art,Hima,

First thank you for your help.

Second ,I have to apologize.I have thought of the %let method, but instinctly thought it won't work.

I should at least have a try, cause it is so easy to resolve if I had a try..

Thank you both for your patience to give a hand to a newbie especially on a so silly question.

Hima
Obsidian | Level 7

Code:

%let var1=name,age;
proc sql;
create table test1 as select &var1., sex from sashelp.class;
quit;

proc sql;
create table test2 as select &var1., weight from sashelp.class;
quit;

proc sql;
create table test3 as select &var1., height from sashelp.class;
quit;

data final;
set test1 test2 test3;
run;

Output:

Alfred14M..
Alice13F..
Barbara13F..
Carol14F..
Henry14M..
James12M..
Jane12F..
Janet15F..
Jeffrey13M..
John12M..
Joyce11F..
Judy14F..
Louise12F..
Mary15F..
Philip16M..
Robert12M..
Ronald15M..
Thomas11M..
William15M..
Alfred14112.5.
Alice1384.
Barbara1398.
Carol14102.5.
Henry14102.5.
James1283.
Jane1284.5.
Janet15112.5.
Jeffrey1384.
John1299.5.
Joyce1150.5.
Judy1490.
Louise1277.
Mary15112.
Philip16150.
Robert12128.
Ronald15133.
Thomas1185.
William15112.
Alfred14.69
Alice13.56.5
Barbara13.65.3
Carol14.62.8
Henry14.63.5
James12.57.3
Jane12.59.8
Janet15.62.5
Jeffrey13.62.5
John12.59
Joyce11.51.3
Judy14.64.3
Louise12.56.3
Mary15.66.5
Philip16.72
Robert12.64.8
Ronald15.67
Thomas11.57.5
William15.66.5

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
  • 793 views
  • 6 likes
  • 4 in conversation