BookmarkSubscribeRSS Feed
Xinxin
Obsidian | Level 7

I  want to use proc corr in data step do loop/array.

%include 'file containing BI variables ';

proc corr data=abc out=xyz noprint;

     where market= array-variable;

     var bac11;

     with &BI1 &BI2;

run;

The dimensions of the array are different for different markets.

I have tried 'call execute' and macros but can't get it. Any ideas?

10 REPLIES 10
art297
Opal | Level 21

Your question will be easier to answer if you show your include file and the code you have already attempted.

Tom
Super User Tom
Super User

Unless you use PROC FCMP to make a function you cannot call PROC CORR inside of a DATA STEP.

Perhaps instead you want to transpose your "array" so that you can call PROC CORR with a BY statement?

art297
Opal | Level 21

Tom,

Of course I agree that you can't "call" proc corr from within a datastep, but you can ultimately run the code you want by writing call execute statements, a macro variable, or a file that can later be "included".  I chose not to suggest any of those until the OP tells us what he/she really wants.

Xinxin
Obsidian | Level 7

This is my code:

%LET TIME_PERIOD='Q42010';

%include 'C:\Documents and Settings\Desktop\Global xxxx SAS\Code\BI_Variables.sas';

%MACRO CORR(MKTNUM);

       proc corr data=mybeat.Respbrand_q42010 outp=mkt&MKTNUM.corr1 NOPRINT;

              WHERE MARKET=&MKTNUM;

              var BAC11 ;

              WITH &BI1ATT &BI2ATT &BI3ATT;

       run;

       DATA mkt&MKTNUM.corr2 (DROP=_TYPE_ _NAME_);

              LENGTH QNUM $40.;

              SET mkt&MKTNUM.corr1;

              WHERE _TYPE_='CORR' /*AND BAC11 NE . */;

              QNUM=_NAME_ ||"_OPINION_CORR";

              TIME_PERIOD=&TIME_PERIOD;

              MARKET=&MKTNUM;

              RENAME BAC11 = PER_UNWTD;

              label BAC11='PER_UNWTD';

       RUN;

%MEND;

%CORR(9)

My %include files looks like this:

%LET BI1ATT=BI1ATT01 BI1ATT02 BI1ATT03 … ;--- 182 variables

%LET BI1ATT=BI1ATT01 BI1ATT02 BI1ATT03 … ;--- 182 variables

%LET BI1ATT=BI1ATT01 BI1ATT02 BI1ATT03 … ;--- 182 variables

I want to find correlations by market and since I don’t know how many markets there will be, I want to put the Proc Corr in a Do loop.

I get the list of distinct markets like this:

PROC SQL ;

SELECT DISTINCT MARKET INTO :listMARKET

SEPARATED BY ", " FROM mybeat.Respbrand_q42010;

QUIT;

This (or using a DO loop) did not work either:

data _null_;

       if  i in (&listMARKET) then do ;

       call execute (

              %CORR(i));

       end;

run;

The Do loop ends everytime it encounters the proc corr or I get a number of other errors.

Tom
Super User Tom
Super User

Why not just use a BY statement? 

proc corr data=mybeat.Respbrand_q42010 outp=mktcorr1 NOPRINT;

   by market;

   var BAC11 ;

   WITH &BI1ATT &BI2ATT &BI3ATT;

run;

DATA mktcorr2 (DROP=_TYPE_ _NAME_);

    LENGTH QNUM $40.;

    SET mktcorr1;

    WHERE _TYPE_='CORR' /*AND BAC11 NE . */;

    QNUM=_NAME_ ||"_OPINION_CORR";

    TIME_PERIOD=&TIME_PERIOD;

    RENAME BAC11 = PER_UNWTD;

    label BAC11='PER_UNWTD';

RUN;

Where does the macro variable TIME_PERIOD come from?

Xinxin
Obsidian | Level 7

Thank you, Tom. Yes, the BY statement gives me the results that I want. However, I was trying to avoid sorting the data as it's a very,very large dataset. But till I come up with something else, this is what I will use. Thanks a lot!

Tom
Super User Tom
Super User

Do you have permission to add an index? 

If you have 10 markets and you loop then you will need to read the large dataset 10 times.

If you add an index then you need to read it only twice.  Once to add the index and then again to run the correlations.  Plus the index will be available for other subset analysis you might want to do.

MikeZdeb
Rhodochrosite | Level 12

Hi ... here's a data step approach using SASHELP.HEART.  It cycles through five different

values of cause of death (in the array) for two different sets of WITH variables (VAR is the age at death).

filename tmp temp;


data _null_;

file tmp;

array cause(5) $3 _temporary_ ('Can' 'Cer' 'Cor' 'Oth' 'Unk');

input (bi1 bi2) (: $10.);

do j=1 to 5;

put 'title "WHERE VALUE: ' cause(j) +10 'WITH: ' bi1 bi2 '";' /

    'proc corr data=sashelp.heart spearman;' /

    'where deathcause eq : "' cause(j)  +(-1) '";' /

    'var ageatdeath;' /

    'with ' bi1 bi2 ';' /

    'ods select spearmancorr;' /

    'run;' / ;

end;

datalines;

diastolic systolic

height weight

;

run;

%include tmp / source2;

If the array values are in some data set, you can use macro variables to "populate" the array statement ...

proc sql noprint;

select count(*) into :ncauses from 

(select distinct deathcause

from sashelp.heart

where deathcause is not missing);

select distinct quote(put(deathcause,$3.)) into :causes separated by ' '

from sashelp.heart

where deathcause is not missing;

quit;

filename tmp temp;

data _null_;

file tmp;

array cause(&ncauses) $3 _temporary_ (&causes);

input (bi1 bi2) (: $10.);

<repeat code uses above>

Xinxin
Obsidian | Level 7

Thanks, MikeZdeb, for your reply.

I got the following proc sql to work:

proc sql noprint;

       select count(*) into :nmarkets from 

              (select distinct market from mysas.NPS_Q42010 where market is not missing);

       select distinct market into :mktlist separated by ' ' from mysas.NPS_Q42010 where market is not missing;

       select count(*) into :nseg from

              (select distinct segment from mysas.NPS_Q42010 where segment is not missing);

       select distinct segment into :seglist separated by ' ' from mysas.NPS_Q42010 where segment is not missing;

quit;

However I cannot get the correct assignments for the arrays. I have tried putting the proc sql inside and outside of the macro. I have also tried with ‘call execute’.Can you identify what the problem is with this macro?

%LET TIME_PERIOD='Q42010';

%include 'C:\Documents and Settings\mpurekar\Desktop\Global BEAT SAS\Code\NAMEPLATE Level\NI_Variables.sas';

%MACRO CORR_NPS();

       data _null_;

             

array mktarray(&nmarkets) _numeric_ (&mktlist);

              array segarray(&nseg) _numeric_ (&seglist);

      

%do i=1 %to &nmarkets;

              %do j=1 %to &nseg;

                    

                     proc corr data=mysas.NPS_Q42010 outp=corrNPS1_&i._&j NOPRINT;

                           WHERE market=mktarray(&i) AND SEGMENT=segarray(&j);

                           var NA11 ;

                           WITH &NI1ATT &NI2ATT &NI3ATT;

                     run;

                     DATA corrNPS1_&i._&j._2 (DROP=_TYPE_ _NAME_);

                           LENGTH QNUM $40.;

                           SET corrNPS1_&i._&j;

                           WHERE _TYPE_='CORR' ;

                           QNUM=_NAME_ ||"_OPINION_CORR";

                           TIME_PERIOD=&TIME_PERIOD;

                            SEGMENT=segarray(&j);

                           RENAME NA11 = PER_UNWTD;

                           label NA11='PER_UNWTD';

                     RUN;

                    

              %end;

       %end;

       run;

%MEND;

%CORR_NPS();

Thanks!!

MikeZdeb
Rhodochrosite | Level 12

Hi ... sorry, no. 

I think that both a macro and call execute add a level of complexity to all this that you avoid using a data step to write SAS code to a temporary file that is included later.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 1641 views
  • 0 likes
  • 4 in conversation