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
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
@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).
Take a look at the car family of functions (e.g.
Art, CEO, AnalystFinder.com
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.
The collect_list function looks like one of the non-existing SAS SQL string summary functions.
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
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.