topic Re: Macros/Loop in SAS Procedures

Macros/Loop

deleted_user — Mon, 30 Aug 2010 06:10:47 GMT

Hi Experts

I have a situation here hope to find solution :

I have a massive data set ;;;below is just an ex:

2009/01 2009/02 2009/03 2009/04 so on
1 CM CM LA LA
2 MC MC MC MC
3 AQ ZZ LL LL
4 AG HG HG HG
5 IJ IJ Ij IJ

I want a macro which can check and tel in which month conversion took place and how many ?? for ex : Customer 1 converted in Mar and had 1 conversion....
Macro which can compare 2009/01 with 2009/02 then 2009/02 with 2009/03 and so on .....

any help is appreciated ..
H

Re: Macros/Loop

andreas_lds — Mon, 30 Aug 2010 06:43:20 GMT

Why is macro required to solve the problem? You should post an example of the required output dataset.

Re: Macros/Loop

deleted_user — Mon, 30 Aug 2010 21:56:34 GMT

The reason Macros is required as I want to do the process repeatedly every week.

The ex: of required outout dataset is :

2009/01 2009/02 2009/03
1 0 1 1
2 0 0 1
3 0 0 1
4
5

I want 0 when no conversion happened and 1 in the month of conversion so that I can roll the results at end how many conversions happened and when ..... 2009/03 months are variables and 1 2 3 4 are customers .....

Re: Macros/Loop

sbb — Mon, 30 Aug 2010 22:34:33 GMT

Suggest a DATA step approach with your "period" SAS variables a Period1, Period2 thru Periodn - then you can easily use an ARRAY to reference the variable set in the current observation, comparing element x+1 to element x in the array and if a value change occurred, assign 1 to a new variable Convert1 thru Convertn.

Scott Barry
SBBWorks, Inc.

Re: Macros/Loop

deleted_user — Mon, 30 Aug 2010 23:35:36 GMT

Thanks... Can you please give little example of codes. I am new to macros/array.
for ex; Data set variables are Local_data109 - Local_data110 uptill Local_data 120.

Can data set read this variable names in sequence of 109/110/111?

H

Re: Macros/Loop

sbb — Tue, 31 Aug 2010 00:15:29 GMT

You will need to be a bit more descriptive with variable names you have assigned to your data - cannot have embedded blanks in variable name, normally. You will need to gain an understanding of your data as it will exist in SAS, then go forward with programming with a DATA step approach - don't suggest macros if not absolutely necessary.

Suggest searching the SAS support http://support.sas.com/ website for SAS-hosted DOC and supplemental technical / conference materials on related topics.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic/post:

intro array site:sas.com

array processing site:sas.com

Re: Macros/Loop

deleted_user — Tue, 31 Aug 2010 01:58:48 GMT

ok. I have worked on some loop formula as data was already in array swquence........ Can you please check what wrong am I doing in this code....

Data Loop;
set loop2;
x=Pord_scale109;
y= Prod_scale120;
Do 109=110 to 120 by 1;
if prod_scale109^=prod_scale110 then convert=1;
else convert=0;
end;
run;

before loop ends, I want to add 1 to var X and var Y so when I go into the loop the next time, I am looking at the next two prod_scale.

How do I do that .....

Thanks
H

Re: Macros/Loop

deleted_user — Tue, 31 Aug 2010 02:18:24 GMT

Sorry here is working code : Only thing left is to add 1 to Var so that when looping it can change the prod scale from 109=110 to 110=111 and so on 111=112

Code:

Data loop;
set loop2;
start=Prod_scale109;
End=Prod_scale120;
do prod_scale = 109 to 120 by 1;
if prod_scale109^=prod_scale110 then conversion = 1;
else conversion = 0;
end;
run;

As mentioned how and where to add 1 so that when it goes into loop again its looking into 110=111 and 111=112 and so on ....

Thanks
H

Re: Macros/Loop

Cynthia_sas — Tue, 31 Aug 2010 03:39:57 GMT

Hi:
There have been many, many previous forum postings on DO loops and ARRAY processing. Searching the forum might be useful before you go much further with your program. I believe there was even a previous forum thread on how to compare one array variable with the next variable in the list.

I think perhaps you have misunderstood how to loop through numbered variables which are being treated as though they are array members. This paper is a good introduction to the topic of array processing and DO loops:
http://support.sas.com/rnd/papers/sgf07/arrays1780.pdf

cynthia

Re: Macros/Loop

Ksharp — Fri, 03 Sep 2010 08:53:57 GMT

Hi. I think transposing your data into longitudinal data would be helpful.
And assumping your variables(i.e. 2009/01 2009/02 2009/03 2009/04...) are all character.
try this. For getting Macro, do it yourself.

give the code next Monday. Message was edited by: Ksharp

Re: Macros/Loop

Patrick — Sun, 05 Sep 2010 09:52:10 GMT

Hi

I believe the code below does more or less what you asked for.

data have;
infile datalines truncover;
input (Customer_ID Prod_scale_200901 Prod_scale_200902 Prod_scale_200903 Prod_scale_200904) (:$8.) ;
datalines;
1 CM CM LA LA
2 MC MC MC MC
3 AQ ZZ LL LL
4 AG HG HG HG
5 IJ IJ Ij IJ
;
run;

proc sql noprint;
select name,
cats('Conversion_',compress(name,,'kd'))
into :Prod_scale_List separated by ' ',
:Conversion_List separated by ' '
from dictionary.columns
where libname='WORK' and memname='HAVE' and upcase(name) like 'PROD^_SCALE^_%' escape '^'
order by upcase(name)
;
quit;

data want (drop=i PROD_scale_:);
set have;
array PRD {*} &Prod_scale_List;
array CONV {*} 8 &Conversion_List;

CONV(1)=0;
do i=2 to dim(PRD);
CONV(i)= PRD(i-1) ^= PRD(i) ;
Conversion_Total=sum(Conversion_Total,CONV(i));
end;

run;

proc print data=want noobs;
run;

I agree with Ksharp that a different data organisation would make your coding life easier ( --> Customer_ID, Date, Prod_Scale). I assume Ksharp will show you how to achieve this.

As for the code above:

CONV(i)= PRD(i-1) ^= PRD(i) ;
This is kind of short hand to get the 0 and 1 you want. What happens here is:
1) Comparison PRD(i-1) ^= PRD(i) results in TRUE or FALSE (-> 1 or 0)
2) Result of comparison is assigned to CONV(i)
- which at a specific iteration of the do loop might be CONV(3), meaning that the value will be assigned to the 3rd element in the array CONV which in the example would be Conversion_200903.

.... like 'PROD^_SCALE^_%' escape '^'
In SQL the '_' in a LIKE comparison has a special meaning (wildcard). To have the '_' treated as nothing else than a underscore the character must be masked. ESCAPE '^' defines that the character ^ is used to mask the next character in a like comparison.

I assume there might be a few more things in the code you don't know yet. They are all in the SAS Doc and easy to find. It's worth to look them up as all of it will serve you well in the future.

HTH
Patrick

P.S: What I missed to mention is that the PROC SQL part reads the list of variables in the HAVE dataset and stores them sorted in a macro variable which is then used to define the elements of the array.
So whatever variables there are in the HAVE dataset they will be used in the array.
In case that you only need certain months (a subset of variables) you just would have to extend the where clause in the SQL selecting only the range of variables you need (based on the date part of the variable names).

Message was edited by: Patrick

Re: Macros/Loop

Ksharp — Mon, 06 Sep 2010 01:53:58 GMT

Hi. Mr. Patrick . long time no see. 🙂

And my code is more simple than yours.
So you understand more about SAS than me.

[pre]
data temp(drop=id);
input (id _2009_01 _2009_02 _2009_03 _2009_04) ( : $ );
datalines;
1 CM CM LA LA
2 MC MC MC MC
3 AQ ZZ LL LL
4 AG HG HG HG
5 IJ IJ Ij IJ
;
run;
proc print;
run;
data patrick;
set temp;
array a{*} _character_;
do i=2 to dim(a);
if a{i} ne a{i-1} then do;
varname= vname(a{i});
output;
end;
end;
keep varname;
run;
proc freq data=patrick;
tables varname/nopercent nocum;
run;
[/pre]

Ksharp

Re: Macros/Loop

Patrick — Mon, 06 Sep 2010 04:08:19 GMT

Hi Ksharp

"long time no see"
I unfortunately happen to have a day job as well...

"And my code is more simple than yours.So you understand more about SAS than me."
I didn't know that we're competing?

....What happened to _2009_01 and the Customer_ID?

Actually: After your last post talking about transposing the data I expected that you'll come up with something like below:

data have;
infile datalines truncover;
input (Customer_ID Prod_scale_200901 Prod_scale_200902 Prod_scale_200903 Prod_scale_200904) (:$8.) ;
datalines;
1 CM CM LA LA
2 MC MC MC MC
3 AQ ZZ LL LL
4 AG HG HG HG
5 IJ IJ Ij IJ
;
run;

data transpose(keep=Customer_ID Date Prod_Scale);
set have;
array PS {*} Prod_scale_: ;
do i=1 to dim(PS);
/* read the numeric part of variable name and transform it into a SAS date value */
Date= input(compress(vname(PS(i)),,'kd'),yymmn6.);
Prod_Scale=PS{i};
output;
end;
run;

data want;
set transpose;
by Customer_ID Date;
format Date yymmn6.;
Conversion= Prod_Scale ^= lag(Prod_Scale);
if first.Customer_ID then Conversion=0;
run;

proc tabulate data=want;
class Customer_ID Date;
var Conversion;
table Customer_ID ALL='Total'
,(Date All='Total')*Conversion*sum=''*f=comma32.;
run;

Cheers
Patrick Message was edited by: Patrick

Re: Macros/Loop

Ksharp — Mon, 06 Sep 2010 05:48:17 GMT

Hi. Patrick You are definitely right, the code you writed is what i mean,but there are too long code ,So i write the code before. The longitude data is very useful when you meet a trouble.
And unfortunately I also have much job to complete,and have little time to browse this forum.But I love this forum.From it I learned much more than i have.

Ksharp