BookmarkSubscribeRSS Feed
ADK
Calcite | Level 5 ADK
Calcite | Level 5

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ADK
Calcite | Level 5 ADK
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

ADK
Calcite | Level 5 ADK
Calcite | Level 5
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

ballardw
Super User

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

ADK
Calcite | Level 5 ADK
Calcite | Level 5

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

asasuser
Calcite | Level 5

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 ;

SveinE
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1221 views
  • 0 likes
  • 5 in conversation