topic Re: Does anyone have any idea on how to solve this? in SAS Programming

Does anyone have any idea on how to solve this?

ElleMorel — Sat, 23 Nov 2013 05:46:10 GMT

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. The naming convention for the sample data sets follows its

corresponding country*mktseg*tech<n>
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);

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

Patrick — Sat, 23 Nov 2013 07:46:51 GMT

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.

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

ElleMorel — Sun, 24 Nov 2013 20:56:42 GMT

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

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

Patrick — Sun, 24 Nov 2013 21:17:24 GMT

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?

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

ElleMorel — Sun, 24 Nov 2013 22:10:11 GMT

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.

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

Patrick — Mon, 25 Nov 2013 02:27:44 GMT

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?

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

ElleMorel — Mon, 25 Nov 2013 03:54:34 GMT

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

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

Patrick — Mon, 25 Nov 2013 05:07:32 GMT

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?

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

ElleMorel — Mon, 25 Nov 2013 06:25:13 GMT

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.

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

Patrick — Mon, 25 Nov 2013 08:47:19 GMT

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;

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

ElleMorel — Mon, 25 Nov 2013 18:05:37 GMT

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

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

ElleMorel — Wed, 27 Nov 2013 19:32:10 GMT

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