topic macro with "numerator" doesn't work in SAS Programming

macro with "numerator" doesn't work

deleted_user — Sun, 18 Jul 2010 14:18:58 GMT

Dear SAS support,

I have a question concerning a macro I want to apply:
I have data that contains information about the brands someone has bought. The variable I'm refering to is called brand in my dataset, and it can have values from 1 to 33, because their are 33 different brands.
Now I want to have the price information of every brand in each line, so I'm transposing the file. Since I don't want write down 33 different conditions, I wanted to use a macro. I introduced a local variable named numerator, that should count from 1 to 33 (which is _number). But it doesn't seem to work. I never get the right prices in the new price_br&numerator variables, but just "."s. Can somebody help me and tell me what I'm doing wrong here?

%LET _number = 33;

PROC TRANSPOSE DATA = data_01 OUT = data_02;
BY id week;
ID brand;
VAR price_brand;
RUN;

%MACRO _macro12;
%LOCAL numerator;
DATA data_03;
SET data_02;
DROP _name_ _label_;
%DO numerator = 1 %TO &_nobr.;
price_br&numerator. = _&numerator.;
DROP _&numerator.;
IF price_br&numerator. EQ . THEN regpr&numerator. = 0;
%END;
RUN;
%MEND;
%_macro12;

Regards,
yel10orblu

Re: macro with "numerator" doesn't work

Cynthia_sas — Sun, 18 Jul 2010 14:46:34 GMT

Hi:
Two questions:
1) where does the value for &_nobr macro variable come from??? (shown in the line: %DO numerator = 1 %TO &_nobr.; )

2) Can you show a small sample of what your input data looks like and what your transposed data looks like? Posting a small PROC PRINT of the DATA_01 before the transpose and the DATA_02 after the transpose would work nicely.

cynthia

Re: macro with "numerator" doesn't work

deleted_user — Sun, 18 Jul 2010 15:40:13 GMT

Dear cynthia,

1) I'm sorry, the &_nobr of course has to be &_number (%DO numerator = 1 %TO &_number.;) ... I changed the variable names so that it yould be easier for someone who doesn't know the data but forgot that one.

2)

input file data_01

Obs ID brand WEEK price_brand
1 1 2 14274 .
2 1 3 14274 30.7454
3 1 5 14274 .
4 1 6 14274 .
5 1 7 14274 .
6 1 8 14274 .
7 1 2 14277 .
8 1 3 14277 30.7454
9 1 5 14277 .
10 1 6 14277 .
11 1 7 14277 .
12 1 8 14277 .
13 1 2 14280 .
14 1 3 14280 30.7454
15 1 5 14280 .
16 1 6 14280 .
17 1 7 14280 .
18 1 8 14280 .
19 1 2 14290 .
20 1 3 14290 30.7454
21 1 5 14290 .
22 1 6 14290 .
23 1 7 14290 .
24 1 8 14290 .
25 1 2 14299 .
26 1 3 14299 30.7454
27 1 5 14299 .
28 1 6 14299 15.4605
29 1 7 14299 .
30 1 8 14299 .
31 1 2 14314 .
32 1 3 14314 30.7454
33 1 5 14314 .
34 1 6 14314 15.4605
35 1 7 14314 .
36 1 8 14314 .
37 1 2 14334 .

transposed file data_02

Obs ID WEEK _NAME_ _LABEL_ 2 3 5 6 7 8 1 4
1 1 14274 price_brand price . 30.7454 . . . . . .
2 1 14277 price_brand price . 30.7454 . . . . . .
3 1 14280 price_brand price . 30.7454 . . . . . .
4 1 14290 price_brand price . 30.7454 . . . . . .
5 1 14299 price_brand price . 30.7454 . 15.4605 . . . .
6 1 14314 price_brand price . 30.7454 . 15.4605 . . . .
7 1 14334 price_brand price . 30.7454 . 15.4605 . . . .
8 1 14343 price_brand price . 30.7454 . 15.4605 . . . .
9 1 14344 price_brand price . 30.7454 . 15.4605 . . . .
10 1 14349 price_brand price . 30.7454 . 15.4605 . . . .
11 1 14351 price_brand price . 30.7454 . 15.4605 . . . .

Does that help you? Let me know if you need anything else...

Re: macro with "numerator" doesn't work

Cynthia_sas — Sun, 18 Jul 2010 17:03:13 GMT

Hi:
I'm curious about several things...
1) the headers that you show for the transposed data:
[pre]
Obs ID WEEK _NAME_ _LABEL_ 2 3 5 6 7 8 1 4
[/pre]

In the data you show, BRAND only has values of 2, 3, 5, 6, 7 and 8. Yet, in your headers, you also appear to have values for BRAND of 1 and 4??? is this correct??? Is this what you mean when you say you could have 33 -- you could have 33 brands????

2) in your original post you say:
"Now I want to have the price information of every brand in each line, so I'm transposing the file. Since I don't want write down 33 different conditions, I wanted to use a macro." I don't understand what you mean by these terms: "in each line"?? In each observation?? "write down 33 different conditions"?? Where would you write 33 different conditions???

3) Are you sure that the TRANSPOSE you posted generates the DATA_02 results that you posted??? When I use the snippet of data that you provided and use your posted TRANSPOSE code, I get different results. See below. Note that my transposed variables start with _ (underscore) and I only have _2 through _8. It looks like the price for brand 3 is always 30.7454 and the price for brand 6 is always 15.4605 -- is this correct?? Why don't the other brands have prices???

4) Now that you've shown DATA_01 and DATA_02 (assuming that one of our posts of DATA_02 is correct), can you show a "dummy" version of DATA_03 and what your final desired TABLE should look like????

cynthia

RESULTS:
[pre]
Obs ID week _NAME_ _2 _3 _5 _6 _7 _8

1 1 14274 price_brand . 30.7454 . . . .
2 1 14277 price_brand . 30.7454 . . . .
3 1 14280 price_brand . 30.7454 . . . .
4 1 14290 price_brand . 30.7454 . . . .
5 1 14299 price_brand . 30.7454 . 15.4605 . .
6 1 14314 price_brand . 30.7454 . 15.4605 . .
7 1 14334 price_brand . . . . . .
[/pre]

Re: macro with "numerator" doesn't work

deleted_user — Sun, 18 Jul 2010 17:25:22 GMT

Hi cynthia,

thank you so much for your quick reply.

1) Yes, I do indeed have values for brand 1 and 4, and also for all the brands up to 33 brands.

2) It's the macro that doesn't work for me. The line price_br&numerator. = _&numerator.; doesn't seem to work. So if I would have to do it "the complicated way", I would have to write down price_br1 = 1; price_br2=2 and so on (33 times). And since I have more variables that need to be transposed (for example, data on advertising), that would be some really hard work. And please excuse my bad English: Yes, with each line I mean each observation.

3) The results you're getting here are perfectly fine. The prices are the same for all the weeks because there was no price cut (that's different for other observations in the dataset). The other brands don't have prices here because ID refers to a certain store that carries only certain brands. Most other stores carry more brands.

4)

Obs__ID__week____price_br1____price_br2_____price_br3_____ ... ___price_br33
1____1___14274___0___________0____________30.7454_______ ...___24.3342__
2____1___14277___0___________0____________30.7454_______ ...___24.3342__
3____1___14280___0___________0____________30.7454_______ ...___24.3342__
4____1___14290___0___________0____________30.7454_______ ...___24.3342__
5____1___14277___0___________0____________30.7454_______ ...___24.3342__
6____1___14314___0___________0____________30.7454_______ ...___24.3342__

Also, sorry again, there was another mistake in the code. Instead of regpr it should be price_br of course...

Here's the updated code:

%LET _number = 33;

PROC TRANSPOSE DATA = data_01 OUT = data_02;
BY id week;
ID brand;
VAR price_brand;
RUN;

%MACRO _macro12;
%LOCAL numerator;
DATA data_03;
SET data_02;
DROP _name_ _label_;
%DO numerator = 1 %TO &_numerator.;
price_br&numerator. = _&numerator.;
DROP _&numerator.;
IF price_br&numerator. EQ . THEN price_br&numerator. = 0;
%END;
RUN;
%MEND;
%_macro12;

Re: macro with "numerator" doesn't work

sbb — Sun, 18 Jul 2010 17:50:19 GMT

What generates the macro variable called &_numerator? Is that your _number?

Does this macro even execute with success, given this comment?

Also, consider this forum is *NOT* SAS Support, though the fine technical staff at SAS Institute frequently contribute and are quite diligent with input/feedback as subscribers, at all hours of the day/night I might add.

Scott Barry
SBBWorks, Inc.

Re: macro with "numerator" doesn't work

deleted_user — Sun, 18 Jul 2010 17:59:39 GMT

Argh, again a mistake in the code...

%LET _number = 33;

PROC TRANSPOSE DATA = data_01 OUT = data_02;
BY id week;
ID brand;
VAR price_brand;
RUN;

%MACRO _macro12;
%LOCAL numerator;
DATA data_03;
SET data_02;
DROP _name_ _label_;
%DO numerator = 1 %TO &_number.;
price_br&numerator. = _&numerator.;
DROP _&numerator.;
IF price_br&numerator. EQ . THEN price_br&numerator. = 0;
%END;
RUN;
%MEND;
%_macro12;

The _numerator is defined by _number, that's right.

Yes, the macro does execute.
However, I get a message saying: NOTE: Variable _1 is uninitialized. NOTE: Variable _2 is uninitialized. and so on..

I'm sorry if my questions don't belong in that forum, so do you reckon I write an e-mail to the SAS support team?

Re: macro with "numerator" doesn't work

sbb — Sun, 18 Jul 2010 18:22:46 GMT

This type of post is quite typical....however:

You would be best served by posting a reply with your SAS log pasted directly to the forum, nothing less -- consider the time wasted with inconsistencies and that now you are sharing more about the "symptom" you are getting.

More important, you should always consider getting a code-sample to work correctly and then if needed implement other SAS programming techniques that help improve efficiency, here that would be the use of the macro language.

If you do a PROC CONTENTS on the output file from TRANSPOSE or add a PUTLOG _ALL_; to your DATA step, I'm convinced you will see the problem through self-diagnosis along with desk-checking your SAS program with this statement added (MGEN is short for MACROGEN and similar for SGEN and SYMBOLGEN):

OPTIONS SOURCE SOURCE2 MGEN SGEN MLOGIC MPRINT;

Scott Barry
SBBWorks, Inc.

Re: macro with "numerator" doesn't work

Cynthia_sas — Sun, 18 Jul 2010 18:33:04 GMT

Hi:
Scott asks a good question. You show this:
[pre]
%MACRO _macro12;
%LOCAL numerator;
DATA data_03;
SET data_02;
DROP _name_ _label_;
%DO numerator = 1 %TO &_numerator.;
price_br&numerator. = _&numerator.;
[/pre]

Your %LOCAL refers to NUMERATOR as a local macro variable (without any underscores in the name.) This means that the correct reference is &NUMERATOR. And yet, in your code, you show 2 completely different macro variable references: &_NUMERATOR and _&NUMERATOR -- neither of which will be the same as &NUMERATOR. So right off the bat, you have a problem...you have 3 possible macro variables, one of them local and in your code you have this:
%DO numerator = 1 %TO &_numerator.; which seems to me should be:
%DO numerator = 1 %TO &_number.; or
%DO numerator = 1 %TO 33;

And THEN your %LET statement refers to _NUMBER -- a 4th possible macro variable, whose limit is set to 33. And it seems to me that you really want the DO loop to go from 1 to 33, (which is &_NUMBER) and not from 1 to &_NUMERATOR or _&NUMERATOR???? Is that correct???

I understand that you don't like the _2, _3, _4, etc naming convention that you get from PROC TRANSPOSE -- however, that can EASILY be solved by using the PROC TRANSPOSE PREFIX= option which will put the same prefix in front of each TRANSPOSED variable. So, instead of getting _2, _3, etc, you could have PREFIX=PB or PREFIX=PRICE_BR and you would get PB1, PB2, PB3, PB4 or PRICE_BR1, PRICE_BR2, PRICE_BR3, PRICE_BR4 as the names of your variables after the transpose.

As for the issue of how to turn the missing values (.) for transposed prices into 0 that is a fairly simple task for a simple DATA step ARRAY with a simple DO loop - no macro coding required.

I'm not convinced that you really need a SAS Macro solution here. I don't see anything that you could not also accomplish and accomplish easier with a simple DATA step DO loop.

This paper gives a good introduction to simple ARRAY processing with SAS (without using SAS macros)
http://support.sas.com/rnd/papers/sgf07/arrays1780.pdf

cynthia

ps...consider either of these PROC TRANSPOSE steps as an alternative to what you are currently using the code below...note that they create DATA_02A and DATA_02B, so you can compare the 2 output datasets created:
[pre]
PROC TRANSPOSE DATA = data_01 OUT = data_02A(drop=_NAME_)
prefix=pb;
BY id week;
ID brand;
VAR price_brand;
RUN;

PROC TRANSPOSE DATA = data_01 OUT = data_02B(drop=_NAME_)
prefix=price_br;
BY id week;
ID brand;
VAR price_brand;
RUN;
[/pre]

Re: macro with "numerator" doesn't work

sbb — Sun, 18 Jul 2010 19:37:28 GMT

Also, regarding the use of an ARRAY, I quickly scanned the conference paper cited and did not noticee the use of a generic variable prefix list in the ARRAY statement to avoid the need to know how many elements are in the array - here's an example, where I frequently use the approach to reset missing values to zero or just the opposite (zero to missing, with OPTIONS MISSING=' ';) for easier report readability:

DATA _NULL_;
RETAIN NUM1-NUM5 . NUM6-NUM9 0;
ARRAY ANUM (*) NUM: ;
DO I=1 TO DIM(ANUM);
IF ANUM(I) = . THEN ANUM(I) = 0;
END;
PUTLOG _ALL_;
RUN;

Scott Barry
SBBWorks, Inc.

Re: macro with "numerator" doesn't work

Cynthia_sas — Sun, 18 Jul 2010 20:13:22 GMT

Scott:
I thought of that.

However, he says he KNOWS that the count of potential brands is fixed at 33. He sets &_NUMBER to 33. So a DO loop (as opposed to a %DO loop) that goes from 1 to 33 should be OK.

And, besides, if I gave away all the -other- paper references in the first post, I'd have nothing to follow up with!

cynthia

http://www2.sas.com/proceedings/sugi30/242-30.pdf
http://www.stat.berkeley.edu/~spector/array.pdf
http://support.sas.com/resources/papers/proceedings09/032-2009.pdf
http://www.uncg.edu/bae/people/ribar/teaching/ECO725/notes/SAS_arrays.pdf
http://www.afhood.com/blog/?p=234
http://support.sas.com/kb/24/570.html
http://www2.sas.com/proceedings/sugi26/p073-26.pdf (about the use of the colon modifier)
cynthia

Re: macro with "numerator" doesn't work

deleted_user — Mon, 19 Jul 2010 07:50:26 GMT

Thank you both so much for your valuable comments, you really helped me out here!!