Staff_USER_ID | Staff_Name | Target | Manager | Manager_Staff_Id | Lead | Lead_Staff_Id |
---|---|---|---|---|---|---|
SHEE344 | SHEETHAL | 100 | DAVID | DAVID222 | KAY | KAY2223 |
ANJ4343 | ANJANA | 85 | DAVID | DAVID222 | KAY | KAY2223 |
DAVID222 | DAVID | 85 | KAY | KAY2223 | ||
ANUS2472 | Anusha | 100 | Eman | EMAN2032 | KAY | KAY2223 |
LOVE2028 | Lovella | 52 | Eman | EMAN2032 | KAY | KAY2223 |
EMAN2032 | Eman | 85 | KAY | KAY2223 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Dear Friends
Please help me out with proc sql or SAS eg code.
I have source data like below.
Staff USER ID | Staff Name | Target | Manager | Manager Staff Id | Lead | Lead Staff Id |
SHEE344 | SHEETHAL | 100% | DAVID | DAVID222 | KAY | KAY2223 |
ANJ4343 | ANJANA | 85% | DAVID | DAVID222 | KAY | KAY2223 |
DAVID222 | DAVID | 85% | KAY | KAY2223 |
I want output of their id's like below. Target of manager must be accessible by all staff.
Staff USER ID | Staff Name | Target | Manager | Manager Staff Id | Lead | Lead Staff Id | Access UIDS |
SHEE344 | SHEETHAL | 100% | DAVID | DAVID222 | KAY | KAY2223 | SHEE344,DAVID222,KAY2223 |
ANJ4343 | ANJANA | 85% | DAVID | DAVID222 | KAY | KAY2223 | ANJ4343,DAVID222,KAY2223 |
DAVID222 | DAVID | 85% | KAY | KAY2223 | SHEE344,ANJ4343,DAVID222,KAY2223 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You probably looking for this
%let g_staff=; /* Avoid a note in the log */
DATA want(drop=rc);
SET have;
LENGTH Access_UIDS $400;
rc = dosubl('%let g_staff=; proc sql noprint; select Staff_USER_ID into :g_staff separated by "," from work.have where Manager_Staff_Id="'||STRIP(Staff_USER_ID)||'";quit;');
Access_UIDS = CATX(',',symget('g_staff'),Staff_USER_ID,Manager_Staff_Id,Lead_Staff_Id);
RUN;
in order to generate this
Staff_USER_ID | Staff_Name | Target | Manager | Manager_Staff_Id | Lead | Lead_Staff_Id | Access_UIDS |
---|---|---|---|---|---|---|---|
SHEE344 | SHEETHAL | 100 | DAVID | DAVID222 | KAY | KAY2223 | SHEE344,DAVID222,KAY2223 |
ANJ4343 | ANJANA | 85 | DAVID | DAVID222 | KAY | KAY2223 | ANJ4343,DAVID222,KAY2223 |
DAVID222 | DAVID | 85 | KAY | KAY2223 | SHEE344,ANJ4343,DAVID222,KAY2223 | ||
ANUS2472 | Anusha | 100 | Eman | EMAN2032 | KAY | KAY2223 | ANUS2472,EMAN2032,KAY2223 |
LOVE2028 | Lovella | 52 | Eman | EMAN2032 | KAY | KAY2223 | LOVE2028,EMAN2032,KAY2223 |
EMAN2032 | Eman | 85 | KAY | KAY2223 | ANUS2472,LOVE2028,EMAN2032,KAY2223 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi @SASUserRocks ,
Use CATX function in data step:
Access_UIDS= catx(',', SAS_USER_ID, Manager_Staff_Id, Lead_Staff_Id);
Or proc sql;
select*, catx(',', SAS_USER_ID, Manager_Staff_Id, Lead_Staff_Id) as Access_UIDS
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Can you edit your question to describe the logic?
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
uid
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, still not clear. I suggest to write out the rules. Or write out the logic for each record.
For the first record, SHEE34, their manager is DAVID. The Access UIDs are for SHEE34, their manager, and lead staff ID.
Same rule for the second record.
For the third record, DAVID22, both SHEE34 and ANJ343 are included in Access UIDs, I guess because David is there manager.
What would the row for Kay look like?
What would the row for Kay's manager look like?
How many levels of nesting are there?
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This should give what you are looking for
DATA have;
LENGTH
Staff_USER_ID $8
Staff_Name $20
Target 3
Manager $20
Manager_Staff_Id $8
Lead $20
Lead_Staff_Id $8;
INFILE DATALINES missover;
INPUT
Staff_USER_ID $
Staff_Name $
Target
Manager $
Manager_Staff_Id $
Lead ? $
Lead_Staff_Id $;
datalines;
SHEE344 SHEETHAL 100 DAVID DAVID222 KAY KAY2223
ANJ4343 ANJANA 85 DAVID DAVID222 KAY KAY2223
DAVID222 DAVID 85 KAY KAY2223
;
RUN;
DATA want(drop=rc);
SET have;
LENGTH Access_UIDS $400;
rc = dosubl('proc sql noprint; select Staff_USER_ID into :g_staff separated by "," from work.have where Manager_Staff_Id="'||STRIP(Staff_USER_ID)||'";quit;');
Access_UIDS = CATX(',',symget('g_staff'),Staff_USER_ID,Manager_Staff_Id,Lead_Staff_Id);
RUN;
Just change the variables names to fit your data.
Hope this helps
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
%let g_staff=; /* Avoid a note in the log */
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
can you guide when datalines are as below.Access uids not coming as expected with given code
SHEE344 SHEETHAL 100 DAVID DAVID222 KAY KAY2223
ANJ4343 ANJANA 85 DAVID DAVID222 KAY KAY2223
DAVID222 DAVID 85 KAY KAY2223
ANUS24722 Anusha 100 Eman EMAN20327 KAY KAY2223
LOVE20282 Lovella 52 Eman EMAN20327 KAY KAY2223
EMAN20327 Eman 85 KAY KAY2223
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This is what I get when I update the datalines
datalines;
SHEE344 SHEETHAL 100 DAVID DAVID222 KAY KAY2223
ANJ4343 ANJANA 85 DAVID DAVID222 KAY KAY2223
DAVID222 DAVID 85 KAY KAY2223
ANUS24722 Anusha 100 Eman EMAN20327 KAY KAY2223
LOVE20282 Lovella 52 Eman EMAN20327 KAY KAY2223
EMAN20327 Eman 85 KAY KAY2223
;
Staff_USER_ID | Staff_Name | Target | Manager | Manager_Staff_Id | Lead | Lead_Staff_Id | Access_UIDS |
---|---|---|---|---|---|---|---|
SHEE344 | SHEETHAL | 100 | DAVID | DAVID222 | KAY | KAY2223 | SHEE344,DAVID222,KAY2223 |
ANJ4343 | ANJANA | 85 | DAVID | DAVID222 | KAY | KAY2223 | ANJ4343,DAVID222,KAY2223 |
DAVID222 | DAVID | 85 | KAY | KAY2223 | SHEE344,ANJ4343,DAVID222,KAY2223 | ||
ANUS2472 | Anusha | 100 | Eman | EMAN2032 | KAY | KAY2223 | SHEE344,ANJ4343,ANUS2472,EMAN2032,KAY2223 |
LOVE2028 | Lovella | 52 | Eman | EMAN2032 | KAY | KAY2223 | SHEE344,ANJ4343,LOVE2028,EMAN2032,KAY2223 |
EMAN2032 | Eman | 85 | KAY | KAY2223 | ANUS2472,LOVE2028,EMAN2032,KAY2223 |
What's your expected output if it was not this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You probably looking for this
%let g_staff=; /* Avoid a note in the log */
DATA want(drop=rc);
SET have;
LENGTH Access_UIDS $400;
rc = dosubl('%let g_staff=; proc sql noprint; select Staff_USER_ID into :g_staff separated by "," from work.have where Manager_Staff_Id="'||STRIP(Staff_USER_ID)||'";quit;');
Access_UIDS = CATX(',',symget('g_staff'),Staff_USER_ID,Manager_Staff_Id,Lead_Staff_Id);
RUN;
in order to generate this
Staff_USER_ID | Staff_Name | Target | Manager | Manager_Staff_Id | Lead | Lead_Staff_Id | Access_UIDS |
---|---|---|---|---|---|---|---|
SHEE344 | SHEETHAL | 100 | DAVID | DAVID222 | KAY | KAY2223 | SHEE344,DAVID222,KAY2223 |
ANJ4343 | ANJANA | 85 | DAVID | DAVID222 | KAY | KAY2223 | ANJ4343,DAVID222,KAY2223 |
DAVID222 | DAVID | 85 | KAY | KAY2223 | SHEE344,ANJ4343,DAVID222,KAY2223 | ||
ANUS2472 | Anusha | 100 | Eman | EMAN2032 | KAY | KAY2223 | ANUS2472,EMAN2032,KAY2223 |
LOVE2028 | Lovella | 52 | Eman | EMAN2032 | KAY | KAY2223 | LOVE2028,EMAN2032,KAY2223 |
EMAN2032 | Eman | 85 | KAY | KAY2223 | ANUS2472,LOVE2028,EMAN2032,KAY2223 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Interesting use of DOSUBL, I am going to have to study this.
cc: @Quentin @AllanBowe @ChrisHemedinger @FreelanceReinh @yabwon
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Indeed interesting, but instead of N data reads (here 6) with SQL, I dare to propose something else:
proc sort data=have;
by Staff_USER_ID;
run;
proc sort data=have out=have1;
by Manager_Staff_Id;
run;
proc transpose data=have1 out=have2(drop=_name_ rename=(Manager_Staff_Id=Staff_USER_ID)) prefix=staff_list_;
by Manager_Staff_Id;
var Staff_USER_ID;
run;
proc print; run;
data want;
merge have(in=h) have2;
by Staff_USER_ID;
array s[*] Staff_USER_ID Manager_Staff_Id Lead_Staff_Id staff_list_:;
drop staff_list_:;
Access_UIDS = CATX(',',of s[*]);
if h;
run;
proc print; run;
Bart
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug
"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings
SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Are you just trying to do this?
DATA have;
LENGTH
Staff_USER_ID $8
Staff_Name $20
Target 3
Manager $20
Manager_Staff_Id $8
Lead $20
Lead_Staff_Id $8
;
INFILE DATALINES truncover ;
INPUT Staff_USER_ID -- Lead_Staff_Id;
cards;
SHEE344 SHEETHAL 100 DAVID DAVID222 KAY KAY2223
ANJ4343 ANJANA 85 DAVID DAVID222 KAY KAY2223
DAVID222 DAVID 85 KAY KAY2223
ANUS24722 Anusha 100 Eman EMAN20327 KAY KAY2223
LOVE20282 Lovella 52 Eman EMAN20327 KAY KAY2223
EMAN20327 Eman 85 KAY KAY2223
;
proc sort;
by manager_staff_id;
run;
data want;
do _n_=1 by 1 until(last.manager_staff_id);
set have;
by manager_staff_id;
length Access_UIDS $200;
Access_UIDS = catx(',',Access_UIDS,Staff_USER_ID);
end;
do _n_=1 to _n_;
set have;
output;
end;
run;
proc print;
run;
Result
If you want to include the manager and lead in the list then add this line between the two DO loops.
Access_UIDS = catx(',',manager_staff_id,Lead_Staff_Id,Access_UIDS);