multiple row value in single column comma separated

[AnjaliRocks]

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

[A_Kh]

Hi @AnjaliRocks , 

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

[AnjaliRocks]

I don't think this will club all staff user id to one row against a manager.

[Quentin]

Can you edit your question to describe the logic?

[AnjaliRocks]

I want to list all staff userids under a manager against manager's access
uid

[Quentin]

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?

[AhmedAl_Attar]

Hi @AnjaliRocks 

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]

Just add this line before the second data step
%let g_staff=; /* Avoid a note in the log */

[AnjaliRocks]

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]

Hi @AnjaliRocks 

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 · Accepted solution]

@AnjaliRocks 

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]

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

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

[yabwon]

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

[Tom]

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