## Does anyone have any idea on how to solve this?

Occasional Contributor
Posts: 8

# Does anyone have any idea on how to solve this?

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,

1. 1. The naming convention for the sample data sets follows its

corresponding country*mktseg*tech<n>

2. 2. For each sample, tech<n>_flag_now variable contains a 50/50 ratio

of 0’s and 1’s (using all 1’s, and if the count of 1’s > count 0’s, then

50/50 ratio condition can be dismissed).

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);

Posts: 4,737

## Re: Does anyone have any idea on how to solve this?

Posted in reply to ElleMorel

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.

Occasional Contributor
Posts: 8

## Re: Does anyone have any idea on how to solve this?

I don't have any idea on what to be used here, maybe CALLSYMPUT()?. Do u have any? Appreciated!!

Posts: 4,737

## Re: Does anyone have any idea on how to solve this?

Posted in reply to ElleMorel

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?

Occasional Contributor
Posts: 8

## Re: Does anyone have any idea on how to solve this?

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.

Posts: 4,737

## Re: Does anyone have any idea on how to solve this?

Posted in reply to ElleMorel

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?

Occasional Contributor
Posts: 8

## Re: Does anyone have any idea on how to solve this?

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!!

Posts: 4,737

## Re: Does anyone have any idea on how to solve this?

Posted in reply to ElleMorel

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?

Occasional Contributor
Posts: 8

## Re: Does anyone have any idea on how to solve this?

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.

Posts: 4,737

## Re: Does anyone have any idea on how to solve this?

Posted in reply to ElleMorel

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 */

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;

Occasional Contributor
Posts: 8

## Re: Does anyone have any idea on how to solve this?

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!!!

Occasional Contributor
Posts: 8

## Re: Does anyone have any idea on how to solve this?

Hello, Patrick, I would like to wish you and your whole family a wonderful Thanksgiving holiday if you are in the U.S.

Discussion stats
• 11 replies
• 981 views
• 0 likes
• 2 in conversation