Length and concat in PROC SQL SAS

0
votes

I want to define length for some particular columns in select statement and i want to concatenate the two columns i.e sponsor id and sponsor like "ABC-123" in SAS proc sql . Please help here is the code 

proc sql;
select
    project_id,
    sponsor_id,

    empl_country,
    region,
    empl_dept_descr,
    empl_bu_descr,

    sponsor,
    full_name,
    mnth_name FROM Stage0;
quit;
2
sasprocproc-sql
What did you try ? Do you know about the SAS string concatenation operator and functions ? – Richard

2 Answers

4
votes

The CATX function will concatenate any number of arguments, of any type, strip the values and place a common delimiter (also stripped) between each. For example:

proc sql;
  create table want as
  select 
    catx('-', name, age) as name_age length=20
  , catx(':', name, sex, height) as name_gender_height
  from sashelp.class;

The length of a new variable will be 200 characters if the variable the CATX result is being assigned to does not have a length specified.

Stripping means the leading and trailing spaces are removed. Arguments that are missing values do not become part of the concatenation.

SAS Documentation for CATX

1
votes

If you don't know the length you can use a strip() function which will remove leading and trailing spaces, in this case it will remove the spaces generated by the catx() default length:

strip(catx('-', sponsor_id, sponsor))

Code:

proc sql;
select
    project_id,
    sponsor_id,

    empl_country,
    region,
    empl_dept_descr,
    empl_bu_descr,

    sponsor,
    full_name,
    mnth_name ,

/* New column */
strip(catx('-', sponsor_id, sponsor)) as new_id

FROM Stage0;
quit;