data test;
input zone $ state$ brnch$ sales;
cards;
SOUTH AP VZG 254
SOUTH AP TRP 247
SOUTH AP GUN 321
SOUTH TS HYD 147
SOUTH TS WGL 315
NORTH MH MUM 254
NORTH MH PUN 247
NORTH GJ AHM 323
NORTH DL NCR 148
NORTH DL NOD 319
;
I have above dataset . How to create macro variales like see below examples..
ex1:
q1)how to create macro variable for zone distinct count
Ans: 2
q2)how to create macro variable for distinct values of zone
Ans: zone1:SOUTH
ZONE2:NORTH
q3)how to create macro variable for distinct count of state with in zone
Ans: SOUTH_state_1: AP
SOUTH_state_2: TS
NORTH _state_1: MH
NORTH _state_2: GJ
NORTH _state_3 :DL
q3)how to create macro variable for distinct count of branch with in zone and state
Ans: SOUTH_AP_branch1: VZG
SOUTH_AP_branch2: TRP
SOUTH_AP_branch3: GUN
SOUTH_state_TS_branch1: HYD
SOUTH_state_TS_branch2: WGL
...........................................
how to keep in macro
What did you try? What success did you have?
PS: Use the Insert Code button on the menu (look like {i}) when inserting monospaced text. That will keep the formatting and prevent the smileys.
how to keep in macro
What does that mean?
Looks like homework you should be able to do on your own, with a little help from the documentation. Hint: look for call symput() and call symputx().
And Proc Freq with the OUT= tables option.
data test;
input zone $ state$ brnch$ sales;
cards;
SOUTH AP VZG 254
SOUTH AP TRP 247
SOUTH AP GUN 321
SOUTH TS HYD 147
SOUTH TS WGL 315
NORTH MH MUM 254
NORTH MH PUN 247
NORTH GJ AHM 323
NORTH DL NCR 148
NORTH DL NOD 319
;
q1)how to create macro variable for zone distinct count
Ans: 2
q2)how to create macro variable for distinct values of zone
Ans: zone1: SOUTH
ZONE2: NORTH
i have tried below program for creationl of macro variables for zone ....
proc sql;
select count(distinct(zone)) into:zone_count from test ;
select distinct(zone)into:zone1-:zone%left(&zone_count) from test;
quit;
--------------------;
q3)how to create macro variable for distinct count of state with in zone
Ans: SOUTH_state_1: AP
SOUTH_state_2: TS
NORTH _state_1: MH
NORTH _state_2: GJ
NORTH _state_3 DL
i have tried below program for creationl of macro variables for state with zone ....
%macro loop;
%do i=1 %to &zone_count ;
%global &&zone&i%left(1);
proc sql;
select STATE,count(distinct(state)) into:&&zone&i%left(1) from test
where zone="&&zone&i";
quit;
%end;
%mend;
but the above program is not working....
please help how to create separate macro variables
The phrase "keep values with in macro" does not have an obvious meaning to me.
The phrase "but the above program is not working...." requires a lot more information. What is not working? How do you know? Can you run the program again using OPTIONS MPRINT; as the first line, and then show us the LOG by pasting the log into the window that appears when you click on the {i} icon (do not paste the log directly into the reply window).
What do you think the macro code you posted is doing?
What output do you expect?
Can you explain each step in the code?
Remember to use the Insert Code or Insert SAS Code buttons in forum editor so format is preserved.
%macro loop;
%do i=1 %to &zone_count ;
%global &&zone&i%left(1);
proc sql;
select STATE,count(distinct(state)) into:&&zone&i%left(1) from test
where zone="&&zone&i";
quit;
%end;
%mend;
I merged the thread into yesterday's thread with the same question.
If your input data set, though not sorted, is grouped by (zone, state), as it appears to be in your data sample, there's no need to use SQL and reread it multiple times and/or use macros. Simply read it once and use BY processing with a nested DoW-loop:
data test ;
input (zone state brnch) ($) sales ;
cards ;
SOUTH AP VZG 254
SOUTH AP TRP 247
SOUTH AP GUN 321
SOUTH TS HYD 147
SOUTH TS WGL 315
NORTH MH MUM 254
NORTH MH PUN 247
NORTH GJ AHM 323
NORTH DL NCR 148
NORTH DL NOD 319
run ;
data _null_ ;
do qs = 1 by 1 until (last.zone) ;
do until (last.state) ;
set test ;
by zone state notsorted ;
end ;
call symputx (catx ("_", zone, "state", qs), state) ;
end ;
call symputx (catx ("_", "zone", _n_), zone) ;
run ;
%put _user_ ;
The %PUT statement reveals in the log:
GLOBAL ZONE_2 NORTH
GLOBAL ZONE_1 SOUTH
GLOBAL NORTH_STATE_1 MH
GLOBAL NORTH_STATE_2 GJ
GLOBAL NORTH_STATE_3 DL
GLOBAL SOUTH_STATE_1 AP
GLOBAL SOUTH_STATE_2 TS
If your real file is not grouped, sort it by (zone,state) beforehand.
Kind regards
Paul D.
Not quite sure I understand your question. How this other macro variable you're talking about is supposed to be called? Which are the other macro variables whose count it should contain?
Again, you're making me guess. The code can be easily adapted to get any distinct counts within the existing sorted order. Assuming that what you need is the distinct count of (zone) and (zone,state) key combos:
data test ;
input (zone state brnch) ($) sales ;
cards ;
SOUTH AP VZG 254
SOUTH AP TRP 247
SOUTH AP GUN 321
SOUTH TS HYD 147
SOUTH TS WGL 315
NORTH MH MUM 254
NORTH MH PUN 247
NORTH GJ AHM 323
NORTH DL NCR 148
NORTH DL NOD 319
run ;
data _null_ ;
do qs = 1 by 1 until (last.zone) ;
do until (last.state) ;
set test end = lr ;
by zone state notsorted ;
end ;
call symputx (catx ("_", zone, "state", qs), state) ;
q + 1 ;
end ;
call symputx (catx ("_", "zone", _n_), zone) ;
if lr ;
call symputx (catx ("_", "zone_count"), _n_) ;
call symputx (catx ("_", "zone_state_count"), q) ;
run ;
%put _user_ ;
And in the log, you'll get:
GLOBAL ZONE_COUNT 2
GLOBAL ZONE_2 NORTH
GLOBAL ZONE_1 SOUTH
GLOBAL NORTH_STATE_1 MH
GLOBAL NORTH_STATE_2 GJ
GLOBAL NORTH_STATE_3 DL
GLOBAL ZONE_STATE_COUNT 5
GLOBAL SOUTH_STATE_1 AP
GLOBAL SOUTH_STATE_2 TS
Kind regards
Paul D.
Start over from the questions and do this:
1) Show what you think the answer should be for each question if you just calculated the values manually.
Please include details about how you are resolving the ambiguity in the questions to reach those answers.
2) Then show how you attempted to generate those answers with code.
And indicate which ones either generated different answers than you expected or failed to generate any answer.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: