topic Re: Store specific observation in variable with proc sql in New SAS User

Store specific observation in variable with proc sql

Cello23 — Mon, 16 Dec 2019 12:37:58 GMT

hi,

i have a table (A001) like this:

NAMES	CASH
MARIO	100
LUCA	122
MARCO	300

how can I store a specific observation (1° row "MARIO" or 2° row "LUCA" or 3° row "MARCO") of the column "NAMES" of table A001, in the variable FILTER, with "proc sql"?

proc sql noprint ;
select NAMES into : Filter
from A001
quit;

For example, 2° record ( "LUCA" -> FILTER)

thank's

Re: Store specific observation in variable with proc sql

novinosrin — Mon, 16 Dec 2019 12:41:29 GMT

Do you mean

proc sql  noprint ;
select quote(NAMES) into : Filter separated by ','
from A001
quit;

Re: Store specific observation in variable with proc sql

ed_sas_member — Mon, 16 Dec 2019 12:43:08 GMT

Hi @Cello23

It depends on what you want to get:

data A001;
	input NAMES	$ CASH;
	datalines;
MARIO 100
LUCA 122
MARCO 300
;
run;
 
/* &Filter will contain: MARIO LUCA MARCO */

proc sql noprint ;
	select NAMES
	into : Filter separated by " "
	from A001;
quit;

/* &Filter1 will contain MARIO, &Filter2 will contain LUCA, ... */
proc sql noprint ;
	select NAMES
	into : Filter1-:Filter999
	from A001;
quit;

Re: Store specific observation in variable with proc sql

Tom — Mon, 16 Dec 2019 12:47:14 GMT

Sounds like a silly request. SQL does not have a concept or order of data. But SAS does. So you could use dataset options to tell SAS to start reading a dataset at a specific observation number and end at another (equal or greater) observation number.

SQL is not really the right tool for that operation. You will have much more flexibility using normal data step code instead.

data _null_;
 set a001 (firstobs=1 obs=1);
 call symputx('filter',names);
run;

But you can do it in SQL if you must.

select names into :filter trimmed
  from a001(firstobs=1 obs=1)
;

Make sure to use the TRIMMED keyword to prevent the trailing spaces from being stored into the macro variable.

Re: Store specific observation in variable with proc sql

Cello23 — Mon, 16 Dec 2019 13:13:30 GMT

I need to store into variabile FILTER single string, start first observation to last observation (with macro cicle DO WHILE where INDEX are the number of observation table A001):

1° first rows FILTER = 'MARIO"

2° second rows FILTER = 'LUCA'

3° last row FILTER = 'MARCO'

What is the sintax of PROC SQL to store single specific observation (not string) ?

THANk'S!!!

Re: Store specific observation in variable with proc sql

Cello23 — Mon, 16 Dec 2019 13:22:20 GMT

thank you