BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Milo08
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

4 REPLIES 4
Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Opal | Level 21

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

PG
Linlin
Lapis Lazuli | Level 10

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)

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 6685 views
  • 0 likes
  • 3 in conversation