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
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.
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
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;
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
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
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
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 ;
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 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.