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

Question

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!

kl78 · Accepted Answer · 2015-10-07T07:51:00.583

0

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:

edited Oct 07 '15 at 07:51

answered Oct 06 '15 at 20:45

kl78

1,628
1
16
26

So the code is way off. How do I get the results I need? I am very stuck here. As mentioned, I will also be outputting each set of data by person. – S Cross Oct 06 '15 at 21:04
what do you get as result when you just do the changes i mentioned? So far your code looks not bad, just some logical mistakes – kl78 Oct 06 '15 at 21:05
I get back the original table "Want". It seems that no matter what I do, I get that table back. – S Cross Oct 06 '15 at 21:12
@s.Cross : found another mistake, added it to my original answer (point 3). But whit your code you will still get only one dataset with the result for max j if you dont use point 1 – kl78 Oct 06 '15 at 21:17
if I include name =, returns error 180, says name is out of order – S Cross Oct 06 '15 at 21:24
Hm, can you update your exact example code you have now? At the moment i can not see what produces this error... – kl78 Oct 06 '15 at 21:49
@S Cross : tested today the code, it worked they way you want it for me, i pasted the working code in my answer, maybe you can have a look to see whats different in your code – kl78 Oct 07 '15 at 07:45
Thank you! this looks like exactly what I was trying to formulate but kept hitting road blocks, of my own making of course! – S Cross Oct 07 '15 at 13:27
FYI: SAS did not like the "run;" inside the first %do loop, otherwise works perfectly! – S Cross Oct 07 '15 at 20:26
Hm,ok thats strange, it should be there and it makes no problem when i execute at my environment. But important thing is, that it runs the way you want it... – kl78 Oct 07 '15 at 20:41
In a strictly test environment, it works with or without the "run;", but in the monster program I'm using it in, SAS doesn't like it! – S Cross Oct 08 '15 at 14:29

Parfait · Answer 2 · 2015-10-07T03:05:30.497

I partially have a solution using a union query in proc sql which contains subqueries to achieve the anonymous persons name and number.

But as you notice each person is manually entered into each select query. You would hate to do this for say 200 people in your dataset instead of the 5 you show in example. One possibility is to run insert queries mirroring the union's select queries:

proc sql;

create table AnonymousReport As
    SELECT CASE WHEN t1.name = 'Alice' THEN t1.name ELSE CATS('Person',
           (SELECT count(name) + 1 From have t2 
            WHERE t2.name <= t1.name AND t1.name ne t2.name)) END As RptName, 
           t1.Col1Number, t1.Col1Pct, t1.Col2Number, t1.Col2Pct, 'Alice' As ReportToWhom
    FROM have t1

    UNION ALL

    SELECT CASE WHEN t1.name = 'Bob' THEN t1.name ELSE CATS('Person',
           (SELECT count(name) + 1 From have t2 
            WHERE t2.name < t1.name AND t1.name ne t2.name)) END As RptName, 
            t1.Col1Number, t1.Col1Pct, t1.Col2Number, t1.Col2Pct, 'Bob' As ReportToWhom
    FROM have t1

    UNION ALL

    SELECT CASE WHEN t1.name = 'Carol' THEN t1.name ELSE CATS('Person',
           (SELECT count(name) + 1 From have t2 
            WHERE t2.name < t1.name AND t1.name ne t2.name)) END As RptName, 
            t1.Col1Number, t1.Col1Pct, t1.Col2Number, t1.Col2Pct, 'Carol' As ReportToWhom
    FROM have t1

    UNION ALL

    SELECT CASE WHEN t1.name = 'Dave' THEN t1.name ELSE CATS('Person',
           (SELECT count(name) + 1 From have t2 
            WHERE t2.name < t1.name AND t1.name ne t2.name)) END As RptName, 
    t1.Col1Number, t1.Col1Pct, t1.Col2Number, t1.Col2Pct, 'Dave' As ReportToWhom
    FROM have t1

    UNION ALL

    SELECT CASE WHEN t1.name = 'Erin' THEN t1.name ELSE CATS('Person', 
           (SELECT count(name) + 1 From have t2 
            WHERE t2.name < t1.name AND t1.name ne t2.name)) END As RptName, 
    t1.Col1Number, t1.Col1Pct, t1.Col2Number, t1.Col2Pct, 'Erin' As ReportToWhom
    FROM have t1;

quit;

Output Dataset. From here export each person's individual report by the last column ReportToWhom

RptName     Col1Number  Col1Pct Col2Number  Col2Pct    ReportToWhom
Alice       4           15%              8      20%    Alice
Person2     8           30%              6      15%    Alice
Person3     4           15%              8      20%    Alice
Person4     4           15%              8      20%    Alice
Person5     4           15%              8      20%    Alice
Person1     4           15%              8      20%    Bob
Bob         8           30%              6      15%    Bob
Person3     4           15%              8      20%    Bob
Person4     4           15%              8      20%    Bob
Person5     4           15%              8      20%    Bob
...

One possible solution is to use a concatenated insert SQL query across all rows of dataset:

data concat;
    set have;
    length reptAll $3200;
    by name;
    retain reptAll;

    if first.name then reptAll = "";
    unionSQL = "INSERT INTO AnonymousReport (RptName, Col1Number, Col1Pct, Col2Number, Col2Pct, ReportToWhom)
        SELECT CASE WHEN t1.name = '" || name || "' THEN t1.name 
        ELSE CATS('Person', (SELECT count(name) + 1 From have t2 
        WHERE t2.name <= t1.name AND t1.name ne t2.name)) END As RptName, 
        t1.Col1Number, t1.Col1Pct, t1.Col2Number, t1.Col2Pct, '" || name || "' As ReportToWhom
        FROM have t1";

    reptAll =  catx('; ', reptAll, unionSQL) ;

    call symput('query', reptAll);
    if last.name then output;
run;

And then pass string into proc sql macro:

%macro runsql;
    proc sql;   
        &query; 
    quit;
%mend runsql;

%runsql;

In R, I could do this in seconds with its paste/for loop/apply functions but SAS syntax is another world!

While this looks like it will work, kl78's reply is exactly what i was looking for!! Thank you for you help! — S Cross, Oct 07 '15 at 13:28

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

2 Answers2