BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Xamius32
Calcite | Level 5

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



1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
Haikuo
Onyx | Level 15

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

Xamius32
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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

Xamius32
Calcite | Level 5

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/

Tom
Super User Tom
Super User

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;

Xamius32
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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;

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
  • 7 replies
  • 965 views
  • 6 likes
  • 3 in conversation