topic Re: building a string in side of a macro in SAS Programming

building a string in side of a macro

ADK — Thu, 09 Oct 2014 15:06:20 GMT

Hi everyone,

I'm new to SAS and if struggling with strings and combining them. I need to do a sql insert but have charatcter data that needs to be used:

The code I am trying to use is:

**********

%let CallTarg = CALL_TARG;

%let Prod1 = PROD1;

%let Prod2 = PROD2;

%let Prod3 = PROD3;

%let CallTY = CALL_TY;

%let SF = PCR;

%let Terr = TERR;

ilaps = 1 %to 12;

%let FullSF = %str(%'&SF&ilaps%');

proc sql noprint;
select count(distinct &SF&ilaps&Terr) into :NumReps from don.Q215_ACT_ML_PANEL_FINAL_V5;

select sum(&SF&ilaps&CallTarg) into :P1Calls from don.Q215_ACT_ML_PANEL_FINAL_V5 where &SF&ilaps&CallTY= 'A' and &SF&ilaps&Prod1 = 'LN';

select sum(&SF&ilaps&CallTarg) into :P1Calls from don.Q215_ACT_ML_PANEL_FINAL_V5 where &SF&ilaps&CallTY= 'A' and &SF&ilaps&Prod2 = 'LN';

select sum(&SF&ilaps&CallTarg) into :P1Calls from don.Q215_ACT_ML_PANEL_FINAL_V5 where &SF&ilaps&CallTY= 'A' and &SF&ilaps&Prod3 = 'LN';

insert into don.hold

values(&FullSF,&NumReps,&P1Calls,&P2Calls,&P3Calls);

**********

The problem I am having is inserting the FullSF into my output. It should be PCR1 through PCR12 so my insert statement should like this:

insert into don.hold

values('PCR1',10,100,50,25);

but I cannot figure out how to get the quotes arorund the PCR1. The first line inside of the loop is building it correctly according to the log, but my insert statement is not reaading it correctly.

Any help on this specific issue as well as the coding in general (since I am new) would be greatly appreceiated.

Thanks

Re: building a string in side of a macro

RW9 — Thu, 09 Oct 2014 15:14:51 GMT

Hi,

Yes, I think you are maybe going about that the wrong way. Rather than trying to work with the code as presented, could you provide some test data ( in the form of a datastep) and example output thereof. You shouldn't really need the loop and the macro values at all, but it would be easier to explain with an example.

Re: building a string in side of a macro

ADK — Thu, 09 Oct 2014 15:33:17 GMT

RW9,

My output sshould be

SFName NumReps P1Calls P2Calls P3Calls

PCR1 222 141000 100000 110000

PCR2 111 111111 222222 333333

PCR3 333 444444 55555 6666666

PCR12 20 50 100 150

I am not sure how to get yo uthe data. I have been given the sas7bdat file and am just reading from that.

Thanks for the help

Re: building a string in side of a macro

RW9 — Thu, 09 Oct 2014 15:38:07 GMT

Hi,

Just print it and copy paste the first few lines. Anyways I was going for something like (and this is not complete!):

data _null_;

call execute('proc sql; insert into DON.HOLD');

do i=1 to 12;

call execute('set VAR1=(select count(distinct PCR'||strip(put(i,best.))||') from DON.Q215_ACT_ML_PANEL_FINAL_V5),

VAR2=(select sum(PCR'||strip(put(i,best.))||'CallTarg) from don.Q215_ACT_ML_PANEL_FINAL_V5

where PCR'||strip(put(i,best.))||'lapsCall_TY="A" and PCR'||strip(put(i,best.))||'lapsProd1="LN"),

...');

end;

call execute(';quit;');

run;

Re: building a string in side of a macro

ADK — Thu, 09 Oct 2014 15:46:17 GMT

PCR1TERR	PCR1CALL_TARG	PCR1CALL_TY	PCR1PROD1	PCR1PROD2	PCR1PROD3
11110101	16	A	LN	XX	AA
11110102	6	A	XX	LN	AA
11110103	16	A	YY	LN	AA
11110104	8	A	LN	YY	AA
11110101	16	A	AA	LN
11110102	16	A	LN	XX
11110103	12	A	XX	LN
11110104	16	A	YY	LN	AA
11110101	16	A	XX	LN	BB
11110102	16	A	YY	LN	BB
11110103	8	A	LN	XX	BB

Please let me know if this gets you what you need.

Thanks again, I started to look aat your abrevaited code, and have learned new things already.

Thanks

Re: building a string in side of a macro

ballardw — Thu, 09 Oct 2014 15:46:59 GMT

You should be able to get a few rows of data by:

proc print data=<your dataset name goes here> (obs=10);run;

There may be missing code but %do loops generally require being inside a declared macro such as %macro MyMacroName (<parameters>);

Second, then the macro values created inside the macro are local unless defined otherwise

Third, your loop using the same into: macro variable names means that the only ones that exist at the end of the loop are from the last loop execution. This may be what is intended but something to be aware of.

If you need a macro value to resolve within quotes use " instead of ' quotes.

values(&FullSF,&NumReps,&P1Calls,&P2Calls,&P3Calls);

MAYBE should be

values("&FullSF",&NumReps,&P1Calls,&P2Calls,&P3Calls); for example

Re: building a string in side of a macro

ADK — Thu, 09 Oct 2014 15:50:39 GMT

ballardw,

Thanks I will try that.

I saw the into variable being the same. I took the easy way out when creating the post and just copied and pasted. It should be into P1Calls, P2Calls, and P3Calls. That is what I have in my real code.

Thanks

Re: building a string in side of a macro

asasuser — Thu, 09 Oct 2014 19:01:34 GMT

I would recode the SQL and make it simplier: The rest of the macro should be ok or you can modify many of the %Let into parameters

proc sql noprint;

insert into don.hold

select "&FULLSF" as

, count(distinct &SF&ilaps&Terr) as col1

, sum(Case When &SF&ilaps&CallTY= 'A' and &SF&ilaps&Prod1 = 'LN' Then &SF&ilaps&CallTarg Else 0 End) as col2

, sum(Case When &SF&ilaps&CallTY= 'A' and &SF&ilaps&Prod2 = 'LN' Then &SF&ilaps&CallTarg Else 0 End) as col3

, sum(Case When &SF&ilaps&CallTY= 'A' and &SF&ilaps&Prod3 = 'LN' Then &SF&ilaps&CallTarg Else 0 End) as col4

from don.Q215_ACT_ML_PANEL_FINAL_V5 ;

Re: building a string in side of a macro

SveinE — Fri, 10 Oct 2014 06:09:34 GMT

If I have not misunderstood totally, you main problem was building the FullSF string. It should work perfectly well if you add %do before the ilaps part and add wrapping the &FullSF macro variable in %unqote like

values(%unquote(&FullSF),&NumReps,&P1Calls,&P2Calls,&P3Calls);

%macro test;

data test;

length FullSF $10;

delete;

run;

%let SF = PCR;

%do ilaps = 1 %to 12;

%let FullSF = %str(%'&SF&ilaps%');

%put FullSF no &ilaps.: &FullSF.;

proc sql;

insert into test values(%unquote(&FullSF.));

;

quit;

%end;

proc print;

run;

%mend test;

%test;

Proc print result:

1	PCR1
2	PCR2
3	PCR3
4	PCR4
5	PCR5
6	PCR6
7	PCR7
8	PCR8
9	PCR9
10	PCR10
11	PCR11
12	PCR12