Looking for equivalent function is SAS

[dewanganrahul]

Please let me know the equivalent function for string_agg  (which is generally used in SQL Server) in SAS. It is being used in SQL Server as:- Sting_Agg(Variable_Name,';'). Where Variable_Name is any variable in sas and ; is separator. PFB, is the clear picture of how it works

[PaigeMiller]

You could run PROC TRANSPOSE on the variable COLUMN1 to make all the data values in one row, then use the CATX function to perform the string manipulation desired.

UNTESTED CODE

proc transpose data=have out=have_t;
     var column1;
run;
data want;
     set have_t;
     desired_string = catx('-',of col:);
run;

[dewanganrahul]

@PaigeMiller  Can you please provide the solution which can be written under PROC SQL? Since, I am converting the SQL Server queries to PROC SQL code in SAS. FYI: I am writing in the SELECT CLAUSE.

[PaigeMiller]

---

@dewanganrahul wrote:

@PaigeMiller  Can you please provide the solution which can be written under PROC SQL?

---

No, I can't and I'm not sure it is possible in SQL. Maybe someone else can write SQL code to do this. But using the proper tool is also important; SQL seems inefficient here, a poor choice.

Is STRING_AGG not available on your SQL Server? If so, then you should be able to passthru to your SQL server and then use STRING_AGG in your SQL.

[dewanganrahul]

This function is used with one of the variable/column and I am using multiple variable selection with case when statement, so to go for other sas option/tool looks more effort or may not convenient. So hardly looking to check it with same PROC SQL option. If not possible ultimately then I can go for other option. I don't have SQL Server to perform this checks. In-fact, the requirement is to convert the SQL Server queries to SAS code. The best and less effort option, I found is PROC SQL.

[PaigeMiller]

---

@dewanganrahul wrote:

 In-fact, the requirement is to convert the SQL Server queries to SAS code. The best and less effort option, I found is PROC SQL.

---

I disagree. Using the full range of SAS tools and picking the appropriate tool is always going to be more efficient and less effort.

[Sajid01]

Hello @dewanganrahul 
There is no equivalent function in SAS Proc SQL; If you want to implement this in SAS, then the post by   @PaigeMiller' is the right way for you. Or else you have to use SQL pass through (Execute by..).

[maguiremq]

If you're using SAS, it's better to just stick with the built-in procedures that go row-by-row in the PDV. A single DATA step solution would be:

data have;
input col1 :$20.;
datalines;
if
you
want
a
happy
life
save
earth
;
run;

data want (keep = want_var);
	do until (lr);
		length want_var $100.;
		set have end = lr;
		temp = col1;
		want_var = catx('-', want_var, temp);
	end;
run;