topic Re: datasets and macro values in SAS Programming

datasets and macro values

Xamius32 — Fri, 08 Feb 2013 21:01:16 GMT

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

Re: datasets and macro values

Haikuo — Fri, 08 Feb 2013 21:10:04 GMT

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

Re: datasets and macro values

Tom — Fri, 08 Feb 2013 21:23:37 GMT

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

Re: datasets and macro values

Xamius32 — Fri, 08 Feb 2013 21:24:12 GMT

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.

Re: datasets and macro values

Xamius32 — Fri, 08 Feb 2013 21:28:10 GMT

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/

Re: datasets and macro values

Tom — Fri, 08 Feb 2013 21:37:41 GMT

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;

Re: datasets and macro values

Xamius32 — Fri, 08 Feb 2013 21:42:55 GMT

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.

Re: datasets and macro values

Tom — Fri, 08 Feb 2013 21:56:34 GMT

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;