Does anyone have any idea on how to solve this? I have no clue and need desperate help!!
The data set that is produced below
contains 90 observations, 9 groups of 10
obs. where each group is defined by the Country*MktSeg combination. The task
is to create SAS code that will produce samples where,
3. Define the variable “mt” as “CS_tech<n>” if Tech<n>_flag_prior =
0
and “UP_tech<n>” otherwise.
%let Country = BRASIL JAPAN USA;
%let MktSeg = ENT SP SMM;
%macro crt_ds(ds);
data &ds.;
%do i = 1 %to 3;
_Country= scan("&Country.",&i);
%do j = 1 %to 3;
_MktSeg= scan("&MktSeg.",&j);
/*do ModelType='U','C','A';*/
%do tech_indx = 1 %to 10;
Tech1_flag_prior = int(2*ranuni(77777));
Tech2_flag_prior = int(2*ranuni(55555));
Tech1_flag_now= int(2*ranuni(123456));
Tech2_flag_now = int(2*ranuni(99999));
output;
%end; %end; %end;
run;
%mend crt_ds;
%crt_ds(test);
The vast majority in this forum expect you to do your homework by yourself. It's also very possible that one of the members is the teacher which has given you this assignment.
I suggest you give it a go and then when you really get stuck somewhere you post the code you have already developed and tell us the detail problem you need support for.
If you don't know how to get started then at least post all the ideas you already had of how to tackle this so we can point you into the right direction to keep you going.
I don't have any idea on what to be used here, maybe CALLSYMPUT()?. Do u have any? Appreciated!!
As SAS tables can't get created during data step execution (exception: hash tables) you need first to create a macro variable containing a list of the required output tables.
You then can use this list in a consecutive data step.
tip: SQL using select ... Into....separated by ' ' can be used to create and populate such a macro variable.
do you know how to use hash tables?
Thanks for your message, Patrick. Appreciated!! I guess that, since I am new to SAS and the SAS community, I still don't get how in details this real work issue can be done.
This splitting up tables is not awfully difficult but it's also not a beginners task. On which SAS skill level do you see yourself? How much SAS experience do you have?
Thanks for the reply. I am at beginner's level, and due to some part of the work issue, I need to use some SAS. Sorry for that!!
So if I or someone else actually post a fully working solution for the data the macro you've posted creates then is this something you will be able to use even though you might not fully understand how the code works?
Thank you, Patrick, for your reply. See my level in SAS is low so that I cannot write the code at this moment, but I should be able to read, understand, and even treat it as a "sample" program for future uses and changes.
I believe below code is a bit too much for beginner level - but you asked for it so here you go.
I didn't understand your 3rd requirement so this bit is missing. This requirement sounds like some row level logic so this shouldn't be too hard to implement (some if...then...else statements).
For adding additional columns to the output tables: you need to define the columns also in the SQL select statement, eg: SELECT *, . as CS_tech1, . as UP_tech1 FROM ...."
%let Country = BRASIL JAPAN USA;
%let MktSeg = ENT SP SMM;
%macro crt_ds(ds);
data &ds.;
%do i = 1 %to 3;
_Country= scan("&Country.",&i);
%do j = 1 %to 3;
_MktSeg= scan("&MktSeg.",&j);
/*do ModelType='U','C','A';*/
%do tech_indx = 1 %to 10;
Tech1_flag_prior = int(2*ranuni(77777));
Tech2_flag_prior = int(2*ranuni(55555));
Tech1_flag_now= int(2*ranuni(123456));
Tech2_flag_now = int(2*ranuni(99999));
output;
%end;
%end;
%end;
run;
%mend crt_ds;
%crt_ds(test);
proc sql;
create view v_test_sorted as
select *
from test
order by _country, _MktSeg
;
quit;
/* macro definition to pass variable index (1 or 2) so that SAS code needs only to be written once */
%macro Split_It(nr);
/* define hash table */
if _n_=1 then
do;
declare hash h&nr. (dataset:"v_test_sorted(obs=0)",multidata:"y",hashexp:5);
_rc=h&nr..defineKey("Tech&nr._flag_now");
_rc=h&nr..defineData(all:"Y");
_rc=h&nr..defineDone();
end;
/* counter for how many more 0s than 1s in current by group */
retain _Tech&nr._flag_now_Cnt;
_Tech&nr._flag_now_Cnt=sum(_Tech&nr._flag_now_Cnt,-2*Tech&nr._flag_now,1);
/* add current row to hash table */
_rc=h&nr..add();
if last._MktSeg then
do;
/* remove rows until count of 1s >= count of 0s */
do _i=1 to _Tech&nr._flag_now_Cnt;
_rc=h&nr..check(key:0);
_rc=h&nr..removedup(key:0);
end;
/* write hash table to SAS output data set */
_rc=h&nr..output(dataset:cats(_country,"_",_MktSeg,"_","tech&nr."));
/* prep data for iteration of next by group */
_rc=h&nr..clear();
_Tech&nr._flag_now_Cnt=0;
end;
%mend;
options mprint;
data _null_;
set v_test_sorted;
by _country _MktSeg;
/* call macro passing variable index as parameter */
/* this generates SAS code where parameter &n gets replaced by the value passed */
/* eg. "_Tech&nr._flag_now_Cnt" becomes then "_Tech1_flag_now_Cnt" to be processed by the data step */
%Split_It(1)
%Split_It(2)
run;
Hi Patrick, I am really sorry that I just checked and saw it. You are truly an expert! I hope I will understand everything. Deeply appreciated!!!
Hello, Patrick, I would like to wish you and your whole family a wonderful Thanksgiving holiday if you are in the U.S.
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 25. 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.