topic PROC SQL Separated By in SAS Programming

PROC SQL Separated By

Milo08 — Thu, 15 Mar 2012 17:33:22 GMT

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!

PROC SQL Separated By

Linlin — Thu, 15 Mar 2012 18:08:28 GMT

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

PROC SQL Separated By

Milo08 — Thu, 15 Mar 2012 18:32:19 GMT

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!

PROC SQL Separated By

PGStats — Thu, 15 Mar 2012 18:37:19 GMT

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;

PROC SQL Separated By

Linlin — Thu, 15 Mar 2012 19:16:02 GMT

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)