Search code examples
sqlloopssasdata-hidinganonymity

Need to hide some fields for one variable, SAS/SQL

I need to give a report to a group of people summarizing each persons information, but only revealing the name of the person each report is going to. What I have is:

Alice 4 15% 8 20%
Bob 8 30% 6 15%
Carol 4 15% 8 20%
Dave 4 15% 8 20%
Erin 4 15% 8 20%

What I want is:

Alice's Summary
Alice 4 15% 8 20%
Person2 8 30% 6 15%
Person3 4 15% 8 20%
Person4 4 15% 8 20%
Person5 4 15% 8 20%

Bob's Summary
Person1 4 15% 8 20%
Bob 8 30% 6 15%
Person3 4 15% 8 20%
Person4 4 15% 8 20%
Person5 4 15% 8 20%

and so forth.

I've tried several things, given up on having the number after person, and my latest attempt follows:

proc sql;
create table Distinct_People
as select distinct(Name)
from have;
quit;

data People;
set Distinct_People end=no_more;
call symputx('Person'||left(_n_), Name);
if no_more then call symputx('NumPeople', _n_);
run;
quit;

%macro Loop;
%do j=1 %to &NumPeople;
%let Person=Person&j;
data want;
set have;
if Name="&&Person&j" then "&&Person&j";
else "Person";
run;
%end;
%mend Loop;
%Loop;

I know I've probably mangled the above, but really need to figure out how to do this using SAS/SQL either in proc sql or in data steps.

Thank you!

Solution

  • Not sure what you exactly try to do in your macro and without exactly knowing what is all going wrong with your result, i can spot 3 errors in your code:

    1.

    data person;

    in your loop, every time you execute the loop, you overwrite the dataset person. In the next loop you access the overwritten dataset again. So at the end you have only one dataset, and because you have overwritten it every time there is only personx left as names. So you have to write data person&j; to get a single dataset for every person.

    2.

     set person;

    What is the dataset person? you did not define it before in your example. As far as i understand what you are trying to do, you must use set have; here.

    3.

    you have forgotten to overwrite name

     if Name="&&Person&j" then 
 name ="&&Person&j";
 else 
 name ="&Person";

    or simpler version:

    if Name ne "&&Person&j" then 
   name ="&Person";

    4.

    Not an error, but you should use data _null_; instead of data people;, because you are using this datastep only to generate some macrovariables and never use the output anymore, so no need to create a new dataset here.And the quit after the datastep is obsolete...

    Edit:

    i tested today, this code is definitly working for me, if you still get an error i guess you have a typo or something is wrong at your environment:

    data have;

input name $ nr1 nr2 $ nr3 n4 $;
 datalines;
 Alice 4 15% 8 20%
Bob 8 30% 6 15%
Carol 4 15% 8 20%
Dave 4 15% 8 20%
Erin 4 15% 8 20% 
;
run;

proc sql;
create table Distinct_People
as select distinct(Name)
from have;
quit;

data _null_;
set Distinct_People end=no_more;
call symputx('Person'||left(_n_), Name);
if no_more then call symputx('NumPeople', _n_);
run;
quit;

%macro Loop;
%do j=1 %to &NumPeople;
data want&j;
set have;
if Name ne "&&Person&j" then name = cat("Person",_n_);
run;
%end;
%do j=1 %to &NumPeople;
proc print data=want&j; 
title1 " &&Person&j.'s Summary";
run;
%end;
%mend Loop;
%Loop;

    Result:

    enter image description here