R function in SAS

[Kalai2008]

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

[PaigeMiller]

---

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

[art297]

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

CAT, CATT, CATS and CATX )

Art, CEO, AnalystFinder.com

[ballardw]

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

[ChrisNZ]

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

[ChrisNZ]

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

[ChrisNZ · Accepted solution]

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