Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Re: SAS Macro select into function

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-29-2021 02:39 PM
(878 views)

I am using macro function to select certain terms to use in the following programs, and use "%put" function to check whether I have selected correct terms. All I want to select is 201205 (i.e., 2nd row under TERM column) through 202101 (i.e., 28th row under TERM column). However, the output shows that terms selected are 201201-202008. How should I change the program to make correct selection? Here I attached a screen shot of the table I used. Thanks for any feedback!

```
PROC SQL ; *noprint;
SELECT
TERM
INTO :Term2 - :Term28
FROM TERM_REF;
%put &Term2-&Term28;
QUIT;
```

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

If you prefer SQL, try this:

```
PROC SQL ; *noprint;
SELECT
TERM
INTO :Term2 - :Term28
FROM TERM_REF
WHERE 201205 <= Term <= 202101
;
%put &Term2-&Term28;
QUIT;
```

Jim

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

You can use _n_ to track the row numbers first. And then use proc sql.

```
data have;
do term=201201 to 201230;
output;
end;
run;
data keep1;
set have;
row=_n_;
run;
proc sql noprint;
select term into: term2 from keep1 where row=2;
select term into: term28 from keep1 where row=28;
quit;
%put &term2;
%put &term28;
```

If you have more rows then you can automate it using macros. But assuming you need only 2 rows, this is the simplest way.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Depends on WHY you want to start with the second observation.

Do you always want to skip the first observation? Then just exclude it from the input to your SELECT statement.

`FROM TERM_REF(firstobs=2)`

Or do you want to limit by the values you mentioned instead? Are those dates with format that only displays 6 digits? Or are the numbers like 201,201?

`where term_ref >= 201205`

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

If you prefer SQL, try this:

```
PROC SQL ; *noprint;
SELECT
TERM
INTO :Term2 - :Term28
FROM TERM_REF
WHERE 201205 <= Term <= 202101
;
%put &Term2-&Term28;
QUIT;
```

Jim

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

You're welcome.

Incidentally, the code could be made more flexible like this:

```
%LET Start = 201205;
%LET Stop = 202101;
PROC SQL ; *noprint;
SELECT
COUNT(TERM) AS Terms_Retreived
INTO :Terms_Retrieved TRIMMED
FROM TERM_REF
WHERE &Start <= Term <= &Stop
;
SELECT
TERM
INTO :Term1 -
FROM TERM_REF
WHERE &Start <= Term <= &Stop
;
QUIT;
%IF %BQUOTE(&Terms_Retrieved) %THEN
%DO;
%put NOTE: &Term1-&&Term&Terms_Retrieved;
%END;
```

With the above code, you don't need to know in advance how many terms (or which terms) will be retrieved. SAS will figure out how many terms are in the range you specify via the %LET statements, create the appropriate number of macro variables, and display the range in the log for you.

Jim

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.