topic How to use one word to replace a lot of same word to avoid clerical error in SAS Procedures

How to use one word to replace a lot of same word to avoid clerical error

bbb_NG — Tue, 28 Feb 2012 07:58:44 GMT

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.

How to use one word to replace a lot of same word to avoid clerical error

Ksharp — Tue, 28 Feb 2012 09:10:10 GMT

Make another variable :

data have;

set have;

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

run;

How to use one word to replace a lot of same word to avoid clerical error

bbb_NG — Tue, 28 Feb 2012 09:25:20 GMT

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

How to use one word to replace a lot of same word to avoid clerical error

art297 — Tue, 28 Feb 2012 13:29:15 GMT

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;

How to use one word to replace a lot of same word to avoid clerical error

Hima — Tue, 28 Feb 2012 13:54:17 GMT

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

How to use one word to replace a lot of same word to avoid clerical error

bbb_NG — Wed, 29 Feb 2012 01:46:28 GMT

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.