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

Hi,

 

I have a rolled up data set that contains a metric that counts the number of members and Dollars spent:

AgeGroup Region Members Spend

20-24 West 1 50

20-24 East 4 250

20-24 South 2 80

...

For any cell where Members LE 3 I randomize to 0 or 3 and adjust the spend accordingly:

AgeGroup Region Members Spend

20-24 West 0 0

20-24 East 4 250

20-24 South 3 120

 

At the end I evaluate the overall % difference to calclate the possible bias from new to old:

MemberBias = 7/7-1 = 0%

SpendBias = 370/380-1=-2.6%

 

Here's where i'm stuck.

IF MemberBias OR SpendBias is GE +/- 1% re-run the randomization until both is under +-1% then keep that final dataset.

 

I'm not sure how to structure the logic in my code to do this, any help greatly appreciated.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

I suggest using a macro approach. If you create a macro with a %do %until loop you can repeat your data confidentialized & proc sql steps until your bias criteria is met. Something like the following:

 

%macro bias;

%let ok_bias=0;

%do %until (&ok_bias=1);

 

data confidentialized; set rollup(Rename=(Members=tmpMembers Spend=tmpSpend)) ;
If tmpMembers LE 3 then do ;

...

run;

 

*** BIAS Check ;
Proc SQL ;
Create table

...

quit;

 

data _null_;

set BiasCheck;

if memberbias lt .01 and spendbias lt .01 then call symput('ok_bias','1');

run;

 

%end;

%mend;

 

%bias;

View solution in original post

7 REPLIES 7
ballardw
Super User

How are you 'randomizing'?

 

It almost sounds as if you might want to look into Proc Surveyselect and the amount spent might be considered a size measure.

 

 

DangIT
Fluorite | Level 6

Sorry 'Randomizing' may not be the right title for this thread.

 

I am randomizing using Rand("uniform")

 

Data Confidentialized ; Set Rollup(Rename=(Members=tmpMembers Spend=tmpSpend)) ;
If tmpMembers LE 3 then do ;
    Random=rand("Uniform");
    Members=0 ;
    IF Random GT 0.5 then Members=3 ;

 

    Dollars=(Members/tmpMembers)*tmpSpend;
end ;

IF tmpMembers GT 3 then do ; Members =tmpMembers ; Spend=tmpSpend ; end ;
 run ;

*** BIAS Check ;
Proc SQL ;
Create table BiasCheck as SElect
    sum(Members)/sum(tmpMembers)-1 as MemberBias format Percent8.2,
    Sum(Spend)/Sum(tmpSpend)-1 as SpendBias format Percent8.2
From Confidentialized ;
quit;

proc print data=biascheck ; run ;

 

 

Essentially, based on the results of the Bias check, i'd like to re-iterate the confidentialization code so I can 'promote' the version where both bias is less than 1% difference.



I suggest using a macro approach. If you create a macro with a %do %until loop you can repeat your data confidentialized & proc sql steps until your bias criteria is met. Something like the following:

 

%macro bias;

%let ok_bias=0;

%do %until (&ok_bias=1);

 

data confidentialized; set rollup(Rename=(Members=tmpMembers Spend=tmpSpend)) ;
If tmpMembers LE 3 then do ;

...

run;

 

*** BIAS Check ;
Proc SQL ;
Create table

...

quit;

 

data _null_;

set BiasCheck;

if memberbias lt .01 and spendbias lt .01 then call symput('ok_bias','1');

run;

 

%end;

%mend;

 

%bias;

DangIT
Fluorite | Level 6

Thank you for your suggestion, it works great.

How would I alter the code to have a counter for the number of iterations it took to finally be promoted?

 

 

ballardw
Super User

Before the %do intialize your counter and then increment within the loop

%let counter=0;

%do .....;

%let counter = %eval(&counter + 1);

 

 

IF you want to see the value of counter outside the loop you will need to make it GLOBAL variable though.

DangIT
Fluorite | Level 6
Thank you ballardw.

I'm trying to alter the code so the loop will break after 100 iterations OR bias is below the threshold and somehow print out which one was it that kicked it out.

Thanks again for your help.

Give this a try.

 

%macro bias;

%let counter=1;

%let ok_bias=0;

%do %until (&ok_bias=1 or counter gt 100);

 

data confidentialized; set rollup(Rename=(Members=tmpMembers Spend=tmpSpend)) ;
If tmpMembers LE 3 then do ;

...

run;

 

*** BIAS Check ;
Proc SQL ;
Create table

...

quit;

 

data _null_;

set BiasCheck;

if memberbias lt .01 and spendbias lt .01 then call symput('ok_bias','1');

run;

 

%let counter=%eval(&counter + 1);

 

%end;

%put &ok_bias was the ok_bias value;

%put &counter iterations occurred.;

%mend;

 

%bias;

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
  • 1292 views
  • 0 likes
  • 3 in conversation