topic Proc Corr in a data step in SAS Procedures

Proc Corr in a data step

Xinxin — Mon, 25 Jul 2011 20:55:44 GMT

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?

Proc Corr in a data step

art297 — Mon, 25 Jul 2011 21:59:21 GMT

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

Proc Corr in a data step

Tom — Mon, 25 Jul 2011 22:40:03 GMT

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?

Proc Corr in a data step

art297 — Mon, 25 Jul 2011 23:07:16 GMT

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.

Proc Corr in a data step

Xinxin — Wed, 27 Jul 2011 15:16:08 GMT

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

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.

Re: Proc Corr in a data step

Tom — Wed, 27 Jul 2011 15:52:21 GMT

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?

Proc Corr in a data step

Xinxin — Wed, 27 Jul 2011 16:22:39 GMT

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!

Proc Corr in a data step

Tom — Wed, 27 Jul 2011 16:35:38 GMT

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.

Re: Proc Corr in a data step

MikeZdeb — Fri, 29 Jul 2011 17:13:50 GMT

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>

Proc Corr in a data step

Xinxin — Mon, 08 Aug 2011 17:23:13 GMT

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;

run;

%MEND;

%CORR_NPS();

Thanks!!

Re: Proc Corr in a data step

MikeZdeb — Mon, 08 Aug 2011 22:51:46 GMT

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.