BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASUserRocks
Calcite | Level 5

Dear Friends

 

Please help me out with proc sql or SAS eg code.

 

I have source data like below.

 

Staff USER IDStaff NameTargetManagerManager Staff IdLeadLead Staff Id
SHEE344SHEETHAL100%DAVIDDAVID222KAYKAY2223
ANJ4343ANJANA85%DAVIDDAVID222KAYKAY2223
DAVID222DAVID85%KAYKAY2223  

 

I want output of their id's like below. Target of manager must be accessible by all staff.

 

Staff USER IDStaff NameTargetManagerManager Staff IdLeadLead Staff IdAccess UIDS
SHEE344SHEETHAL100%DAVIDDAVID222KAYKAY2223SHEE344,DAVID222,KAY2223
ANJ4343ANJANA85%DAVIDDAVID222KAYKAY2223ANJ4343,DAVID222,KAY2223
DAVID222DAVID85%KAYKAY2223  SHEE344,ANJ4343,DAVID222,KAY2223
1 ACCEPTED SOLUTION

Accepted Solutions
AhmedAl_Attar
Ammonite | Level 13

@SASUserRocks 

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

View solution in original post

13 REPLIES 13
A_Kh
Lapis Lazuli | Level 10

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



SASUserRocks
Calcite | Level 5
I don't think this will club all staff user id to one row against a manager.
Quentin
Super User

Can you edit your question to describe the logic?

The Boston Area SAS Users Group is hosting free webinars!
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.
SASUserRocks
Calcite | Level 5
I want to list all staff userids under a manager against manager's access
uid
Quentin
Super User

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?

The Boston Area SAS Users Group is hosting free webinars!
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.
AhmedAl_Attar
Ammonite | Level 13

Hi @SASUserRocks 

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

AhmedAl_Attar
Ammonite | Level 13
Just add this line before the second data step
%let g_staff=; /* Avoid a note in the log */
SASUserRocks
Calcite | Level 5

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

AhmedAl_Attar
Ammonite | Level 13

Hi @SASUserRocks 

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

 
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?

AhmedAl_Attar
Ammonite | Level 13

@SASUserRocks 

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
PaigeMiller
Diamond | Level 26

Interesting use of DOSUBL, I am going to have to study this.

 

cc: @Quentin @AllanBowe @ChrisHemedinger @FreelanceReinh @yabwon 

--
Paige Miller
yabwon
Onyx | Level 15

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



Tom
Super User Tom
Super User

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

Tom_0-1681837050769.png

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 13 replies
  • 2427 views
  • 7 likes
  • 7 in conversation