DATA Step, Macro, Functions and more

datasets and macro values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

datasets and macro values

This is probably an easy question, but here is what I am trying to do:

*Here is dataset  i AM using in Macro

data test123;

set saslib.megaset;

numvisits=0;

run;

*simple enough macro

%macro Players(Game_ID);

    data TestTable1;

        set test123;

        if game_pk in (&Game_ID)

        then NumVisits+1;

    run;

%mend Players;

*Test macro--here is where I want the value to be a column in a dataset (ie column will have a list of lets say 5-10 game_ids which the macro will then assign numvisits+1 for each of those game_Ids.

%Players(323083);




Accepted Solutions
Solution
‎02-08-2013 04:56 PM
Super User
Super User
Posts: 6,499

Re: datasets and macro values

Perhaps you want to update an existing table.  Say you had a table (GAME_LIST) with a flag variable (GAME_USED) that was initialized to 0 (false).

Then you get a list of games that have been used (GAME_USAGE).   So you could run an SQL UPDATE statement to set the flag variable to 1 (true) when the game is in the new list;

proc sql ;

  update game_list set game_used=1

  where game_pk in (select game_pk from game_usage)

  ;

quit;

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: datasets and macro values

Not sure I completely understand what you are after, but if you remove "numvisits" from your incoming table, the you will have an accumulation on "numvisits" when condition is met. Otherwise, it will be freshed back to '0' every iteration.

Haikuo

Frequent Contributor
Posts: 82

Re: datasets and macro values

I mean instead of just checking one ID (323083 in this case), I want to have a list of IDs that I check in the other dataset and add a numvisit to each observation that contains each ID. One ID might show up 5-6 times, one might show  up 20 times, etc. Does that make sense?

So the %players would ideally have a column name it uses as a list to check.

Super User
Super User
Posts: 6,499

Re: datasets and macro values

The code doesn't look like it is counting anything even though the variable is called NUMVISITS.  Instead it is change the value from 0 to 1 when the value of game_pk matches the passed in value. 

But it sounds like your question is how to pass in more than one value to test against game_pk.  If so then separate the values with spaces in the macro call.

data test123;

  input game_pk @@ ;

  numvisits=0;

cards;

1 2 3 4 5 6

run;

%let game_id=2 3;

data TestTable1;

  set test123;

  if game_pk in (&Game_ID) then NumVisits+1;

run;

Obs    game_pk    numvisits

1        1           0

2        2           1

3        3           1

4        4           0

5        5           0

6        6           0

Frequent Contributor
Posts: 82

Re: datasets and macro values

I get what you are saying. I know I could type in more than one statement in the macro var, but there is a lot so I was hoping it could reference a column of a dataset/

Super User
Super User
Posts: 6,499

Re: datasets and macro values

You could pull the values from the other dataset into a macro variable.  For example if you had another dataset (say GAME_USAGE) that also has a variable named GAME_PK then you could build the macro variable GAME_ID from that dataset.

proc sql noprint ;

  select distinct game_pk into : game_id separated by ' '  from game_usage ;

quit;

But perhaps instead you want to merge the one file with another. Perhaps the first has details of the games (call it GAME_DEFINITION) and the second represents usage of the games.  Now perhaps you have some information that you might want to aggregate into a count.

proc sql ;

  create table usage_counts as

   select a.game_pk,a.game_name, a.game_type, count(b.game_pk) as numvisits

   from game_definition a left join game_usage b

   on a.game_pk = b.game_pk

   group by 1,2,3

   order by 1,2,3

  ;

quit;

Frequent Contributor
Posts: 82

Re: datasets and macro values

I think that first option might work. This is more of a result of an optimization we are running, so the optimization runs, we see what players it chooses, and once a player is seen twice, optimization doesnt try to pick those players.

Solution
‎02-08-2013 04:56 PM
Super User
Super User
Posts: 6,499

Re: datasets and macro values

Perhaps you want to update an existing table.  Say you had a table (GAME_LIST) with a flag variable (GAME_USED) that was initialized to 0 (false).

Then you get a list of games that have been used (GAME_USAGE).   So you could run an SQL UPDATE statement to set the flag variable to 1 (true) when the game is in the new list;

proc sql ;

  update game_list set game_used=1

  where game_pk in (select game_pk from game_usage)

  ;

quit;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 230 views
  • 6 likes
  • 3 in conversation