0

I have the data below. I want to keep unique make variable and concatenate all the information about the unique car maker into one row like this:

!!I want use Proc SQL only to do all the job.

Desire output:

AMC Concord 22 2012-02-19; Pacer 17 2010-09-14; Spirit 22 2011-08-17 
Buick Century 20 2001-12-19; Electra 15 2003-08-02  
Cuick Electra 15 2002-10-01; Century 20 2007-05-30; Spirit  22 2004-11-03

Code:

DATA cars1;
INPUT make $ model $ mpg date YYMMDD10.;
CARDS;
AMC Concord 22 2012-02-19 
AMC Pacer   17 2010-09-14 
AMC Spirit  22 2011-08-17 
Buick Century 20 2001-12-19  
Buick Electra 15 2003-08-02  
Cuick Electra 15 2002-10-01  
Cuick Century 20 2007-05-30  
Cuick Spirit  22 2004-11-03 
;
RUN;

data cars2;
set cars1;
date = put(date,yymmddn8.);
run;

What I tried so far is below:

proc sql;
create table test as
    select  distinct make,model, CATX (",", model,mpg) AS var1,
                        CATX (",", CALCULATED var1, put(date,10.)) AS var2,
                        monotonic() as rownum,
                        max(calculated rownum) as max,min(calculated rownum) as min,
    case 
        when calculated rownum=calculated max then 2  
        when calculated rownum=calculated min then 1 
        else 0 
    end as record,

    case
        when calculated record = 1 then CATS (calculated var2) 
        else CATX (",", CATS (calculated var2),calculated var2) 
    end as result

from cars2
group by make
order by rownum;
quit;
Joe
  • 62,789
  • 6
  • 49
  • 67
user3525837
  • 57
  • 3
  • 9
  • It's probably possible in `proc sql`, but it will be _much_ less efficient and _much_ more complicated than doing it in the datastep. – Joe Sep 03 '15 at 20:01
  • 1
    You'd have to hard code it and do multiple iterations. Are you doing this in SAS or passing it to a database? If it's a database and you can use pass through SQL there may be better options. – Reeza Sep 03 '15 at 20:06
  • 1
    And when I say better options, I mean with regards to SQL. A PROC TRANSPOSE or DATA step are easier to understand and program. – Reeza Sep 03 '15 at 20:11
  • @Joe I'm not sure it is possible using just `proc sql`. i.e. SAS's implementation of SQL. Without some way of collapsing / transposing character values you'd be forced to hardcode a fixed number of self-joins. This might work if you have a handful of records per 'make' but if you have hundreds I'd say that's not a workable solution. – Robert Penridge Sep 03 '15 at 20:44
  • You'd have to either use a fixed number or use the macro facility, though you could use that inside of proc sql entirely. – Joe Sep 03 '15 at 20:57
  • I don't think there are any ansi compliant functions to do this, and SAS's proc sql isn't even compliant. If you are trying to do this on MS SQL Server, you can accomplish it by using STUFF and FOR XML PATH. I suggest reading up on that solution, and then searching for other functions that might accomplish a similar task (but I dont think there are any in SAS) – Josh Sep 24 '15 at 17:10

0 Answers0