topic Re: multiple row value in single column comma separated in SAS Programming

multiple row value in single column comma separated

SASUserRocks — Mon, 17 Apr 2023 12:41:32 GMT

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

Re: multiple row value in single column comma separated

A_Kh — Mon, 17 Apr 2023 13:39:08 GMT

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

Re: multiple row value in single column comma separated

Quentin — Mon, 17 Apr 2023 13:48:10 GMT

Can you edit your question to describe the logic?

Re: multiple row value in single column comma separated

SASUserRocks — Mon, 17 Apr 2023 14:20:29 GMT

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

Re: multiple row value in single column comma separated

SASUserRocks — Mon, 17 Apr 2023 14:21:29 GMT

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

Re: multiple row value in single column comma separated

Quentin — Mon, 17 Apr 2023 14:42:05 GMT

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?

Re: multiple row value in single column comma separated

AhmedAl_Attar — Mon, 17 Apr 2023 14:55:21 GMT

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

Re: multiple row value in single column comma separated

AhmedAl_Attar — Mon, 17 Apr 2023 14:59:23 GMT

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

Re: multiple row value in single column comma separated

SASUserRocks — Tue, 18 Apr 2023 10:40:18 GMT

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

Re: multiple row value in single column comma separated

AhmedAl_Attar — Tue, 18 Apr 2023 11:46:04 GMT

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?

Re: multiple row value in single column comma separated

AhmedAl_Attar — Tue, 18 Apr 2023 11:58:10 GMT

@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

Re: multiple row value in single column comma separated

PaigeMiller — Tue, 18 Apr 2023 16:59:44 GMT

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

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

Re: multiple row value in single column comma separated

Tom — Tue, 18 Apr 2023 17:07:29 GMT

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

Re: multiple row value in single column comma separated

yabwon — Tue, 18 Apr 2023 21:46:34 GMT

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