topic Looking for equivalent function is SAS in SAS Programming

Looking for equivalent function is SAS

dewanganrahul — Thu, 28 Jul 2022 10:53:05 GMT

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

Re: Looking for equivalent function is SAS

PaigeMiller — Thu, 28 Jul 2022 10:57:03 GMT

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;

Re: Looking for equivalent function is SAS

dewanganrahul — Thu, 28 Jul 2022 11:05:44 GMT

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

Re: Looking for equivalent function is SAS

PaigeMiller — Thu, 28 Jul 2022 11:13:53 GMT

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

Re: Looking for equivalent function is SAS

dewanganrahul — Thu, 28 Jul 2022 11:26:22 GMT

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.

Re: Looking for equivalent function is SAS

PaigeMiller — Thu, 28 Jul 2022 11:40:59 GMT

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

Re: Looking for equivalent function is SAS

Sajid01 — Thu, 28 Jul 2022 11:33:50 GMT

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

Re: Looking for equivalent function is SAS

maguiremq — Thu, 28 Jul 2022 12:04:37 GMT

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;