BookmarkSubscribeRSS Feed
ElleMorel
Calcite | Level 5

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

11 REPLIES 11
Patrick
Opal | Level 21

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.

ElleMorel
Calcite | Level 5

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

Patrick
Opal | Level 21

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?

ElleMorel
Calcite | Level 5

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.

Patrick
Opal | Level 21

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?

ElleMorel
Calcite | Level 5

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

Patrick
Opal | Level 21

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?

ElleMorel
Calcite | Level 5

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.

Patrick
Opal | Level 21

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;

ElleMorel
Calcite | Level 5

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

ElleMorel
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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