PROC SQL Separated By

[Milo08]

Hi all,

I am attempting to create a MACRO that will create multiple control files that can be used with other software.  Within the control file I need to create a list of variables associated with each observation.  Here is a version of my code:

PROC SQL;

    SELECT DISTINCT cat(order, '    ', code) INTO :List separated by ' '

    FROM All_Obervations

    WHERE X=&Y. AND U = &V.;

QUIT;

data _null_;

     file "&desktop.\Test_&Y._&V..con" Lrecl=50000;

put "TITLE=Test_&Y._&V.";

put "DATA=&Y._&V..dat";

put '&END';

put "&List";

put "END NAMES";

run;

In the control file I need each observation to be listed on its own line.  For instance:

1     ABCDE

2     BCDEF

3     CDEFG

4     DEFGH

5     EFGHI

PROC SQL seems to be creating a list that just lists all the observations on one line.  For instance,

1     ABCDE   2     BCDEF    3     CDEFG   4     DEFGH   5     EFGHI

How can I get SQL to create a list like the former, instead of the latter?  Is there any kind of SEPARATED BY ENTER command?  Or is there someway I can create a table using PROC SQL that I can name as a MACRO variable?  Thanks in advance!

[Linlin]

Maybe you need more than one macro variable?

data test;

input id name $;

cards;

1 aaa

2 bbb

3 ccc

;

proc sql noprint;

  select DISTINCT cat(id, '    ', name) INTO :list1 -:list3

from test;

quit;

%put &list1; 

%put &list2;

%put &list3;

Linlin

[Milo08]

That is exactly what i am trying at the moment...  but, each iteration of the MACRO will have a different # of observations.  How can I create a do loop that will make the number of put statements unique to each iteration of the macro.  For instance:

data _null_;

     file "&desktop.\Test_&Y._&V..con" Lrecl=50000;

put "TITLE=Test_&Y._&V.";

put "DATA=&Y._&V..dat";

put '&END';

do i=1 to &count.;  /*count variable was created in earlier PROC SQL*/

put "&List&i";

end;

put "END NAMES";

run;

The put statement I underlined is not working.  I get a warning saying symbolic reference not resolved.  Any ideas?  Thanks! 

[PGStats · Accepted solution]

Try this:

PROC SQL;
create table temp as
    SELECT DISTINCT cat(order, '    ', code) as ordername
    FROM All_Obervations
    WHERE X=&Y. AND U = &V.;
QUIT;

data _null_;
set temp end=last;
file "&desktop.\Test_&Y._&V..con" Lrecl=50000;
if _n_ = 1 then do;
put "TITLE=Test_&Y._&V.";
put "DATA=&Y._&V..dat";
put '&END';
end;
put ordername;
if last then put "END NAMES";
run;

PG

[Linlin]

Great job PG!

data All_Obervations;

input order code $ x u;

cards;

1 aaa 5 8

1 bbb 5 8

2 aaa 6 7

2 bbb 6 7

2 aaa 5 8

2 bbb 6 7

;

run;

%macro test(y=,v=);

PROC SQL;

create table temp as

    SELECT DISTINCT cat(order, '    ', code) as ordername

    FROM All_Obervations

    WHERE X=&Y. AND U = &V.;

QUIT;

data _null_;

set temp end=last;

file "c:\temp\Test_&Y._&V..con" Lrecl=50000;

if _n_ = 1 then do;

put "TITLE=Test_&Y._&V.";

put "DATA=&Y._&V..dat";

put '&END';

end;

put ordername;

if last then put "END NAMES";

run;

%mend;

%test(y=5,v=8)