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.
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;
Make another variable :
data have;
set have;
var1=catx(' ',of a1 - a4);
run;
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
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;
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.
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:
Alfred | 14 | M | . | . |
Alice | 13 | F | . | . |
Barbara | 13 | F | . | . |
Carol | 14 | F | . | . |
Henry | 14 | M | . | . |
James | 12 | M | . | . |
Jane | 12 | F | . | . |
Janet | 15 | F | . | . |
Jeffrey | 13 | M | . | . |
John | 12 | M | . | . |
Joyce | 11 | F | . | . |
Judy | 14 | F | . | . |
Louise | 12 | F | . | . |
Mary | 15 | F | . | . |
Philip | 16 | M | . | . |
Robert | 12 | M | . | . |
Ronald | 15 | M | . | . |
Thomas | 11 | M | . | . |
William | 15 | M | . | . |
Alfred | 14 | 112.5 | . | |
Alice | 13 | 84 | . | |
Barbara | 13 | 98 | . | |
Carol | 14 | 102.5 | . | |
Henry | 14 | 102.5 | . | |
James | 12 | 83 | . | |
Jane | 12 | 84.5 | . | |
Janet | 15 | 112.5 | . | |
Jeffrey | 13 | 84 | . | |
John | 12 | 99.5 | . | |
Joyce | 11 | 50.5 | . | |
Judy | 14 | 90 | . | |
Louise | 12 | 77 | . | |
Mary | 15 | 112 | . | |
Philip | 16 | 150 | . | |
Robert | 12 | 128 | . | |
Ronald | 15 | 133 | . | |
Thomas | 11 | 85 | . | |
William | 15 | 112 | . | |
Alfred | 14 | . | 69 | |
Alice | 13 | . | 56.5 | |
Barbara | 13 | . | 65.3 | |
Carol | 14 | . | 62.8 | |
Henry | 14 | . | 63.5 | |
James | 12 | . | 57.3 | |
Jane | 12 | . | 59.8 | |
Janet | 15 | . | 62.5 | |
Jeffrey | 13 | . | 62.5 | |
John | 12 | . | 59 | |
Joyce | 11 | . | 51.3 | |
Judy | 14 | . | 64.3 | |
Louise | 12 | . | 56.3 | |
Mary | 15 | . | 66.5 | |
Philip | 16 | . | 72 | |
Robert | 12 | . | 64.8 | |
Ronald | 15 | . | 67 | |
Thomas | 11 | . | 57.5 | |
William | 15 | . | 66.5 |
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.