topic R function in SAS in SAS Programming

R function in SAS

Kalai2008 — Mon, 20 Jan 2020 21:55:13 GMT

Hello,

I am trying to convert R code to SAS using Proc SQL, The function concat and collectlist could not be located.

I need help in finding an alternative function or code for this R code in SAS.

example:

variable: XXXX-XXXXX

XXX,

XXX-XXXX-XXXX,

XXXXX,

NULL, (blanks) ETC

VARIABLE 2:

example: ( i think length is 12, i cant check since its million records)

XX0000X25X82

XX9001402331

XXX3XX3I01X8

Given R code:

/*concat_ws(',', COLLECT_LIST(distinct coalesce(variable,'')) ) as var1, */

//*concat_ws(',', COLLECT_LIST(distinct substr(coalesce(variable2,'99'),1,2)) ) as var2,*/

Need SAS code:

Thanks for checking

Re: R function in SAS

PaigeMiller — Mon, 20 Jan 2020 22:50:03 GMT

@Kalai2008 wrote:

I am trying to convert R code to SAS using Proc SQL, The function concat and collectlist could not be located.

I need help in finding an alternative function or code for this R code in SAS.

Could you describe for us what the functions CONCAT and COLLECTLIST do? There is no such concept as a LIST in SAS (except in PROC IML).

Re: R function in SAS

art297 — Mon, 20 Jan 2020 23:27:49 GMT

Take a look at the car family of functions (e.g.

CAT, CATT, CATS and CATX )

Art, CEO, AnalystFinder.com

Re: R function in SAS

ballardw — Tue, 21 Jan 2020 19:11:01 GMT

I have to say that I can't tell what your starting values or the associated desired output is for a given input value.

In general with SAS a variable holds a single value per observation (or record if you prefer). So if dealing with multiple values you either have multiple observations or multiple variables in SAS.

It would help if you could show your example of starting SAS data and what the result would look like.

If you have a SAS data set Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Only include the variables/ observations needed to demonstrate the input and the associated output for that example input.

Re: R function in SAS

ChrisNZ — Wed, 22 Jan 2020 05:59:40 GMT

The collect_list function looks like one of the non-existing SAS SQL string summary functions.

Re: R function in SAS

ChrisNZ — Wed, 22 Jan 2020 06:16:05 GMT

In the meantime, and if you insist on using SQL, this may help:

proc sql noprint;

  select distinct quote(coalesce(substr(VAR1, 1, 2), '99')) into :var1list separated by ','
  from TAB1;

  create table TAB2 as
  select catx(',', &var1list) as VAR2
       , etc

Re: R function in SAS

ChrisNZ — Thu, 23 Jan 2020 05:41:59 GMT

Or even:

proc sql noprint;

  select distinct coalesce(substr(VAR1, 1, 2), '99') into :var1list separated by ','
  from TAB1;

  create table TAB2 as
  select "&var1list" as VAR2
       , etc