DATA Step, Macro, Functions and more

building a string in side of a macro

Reply
New Contributor ADK
New Contributor
Posts: 4

building a string in side of a macro

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 Smiley Tongue1Calls from don.Q215_ACT_ML_PANEL_FINAL_V5 where  &SF&ilaps&CallTY= 'A' and &SF&ilaps&Prod1 = 'LN';

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

               select sum(&SF&ilaps&CallTarg) into Smiley Tongue1Calls 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

Super User
Super User
Posts: 7,942

Re: building a string in side of a macro

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.

New Contributor ADK
New Contributor
Posts: 4

Re: building a string in side of a macro

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

Super User
Super User
Posts: 7,942

Re: building a string in side of a macro

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;

New Contributor ADK
New Contributor
Posts: 4

Re: building a string in side of a macro

PCR1TERRPCR1CALL_TARGPCR1CALL_TYPCR1PROD1PCR1PROD2PCR1PROD3
1111010116ALNXXAA
111101026AXXLNAA
1111010316AYYLNAA
111101048ALNYYAA
1111010116AAALN
1111010216ALNXX
1111010312AXXLN
1111010416AYYLNAA
1111010116AXXLNBB
1111010216AYYLNBB
111101038ALNXXBB

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

Super User
Posts: 11,343

Re: building a string in side of a macro

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

New Contributor ADK
New Contributor
Posts: 4

Re: building a string in side of a macro

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

New Contributor
Posts: 3

Re: building a string in side of a macro

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 ;

Occasional Contributor
Posts: 5

Re: building a string in side of a macro

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:    

1PCR1
2PCR2
3PCR3
4PCR4
5PCR5
6PCR6
7PCR7
8PCR8
9PCR9
10PCR10
11PCR11
12PCR12
Ask a Question
Discussion stats
  • 8 replies
  • 425 views
  • 0 likes
  • 5 in conversation