SAS: proc sql - select into

From dataset to one record

data tq84_num_translation;

  input
    val     1- 2
    nam  $  4- 9
    lang $ 11-12
  ;

datalines;
 1 one    en
 2 two    en
 3 three  en
 4 four   en
 5 five   en
 6 six    en
 7 seven  en
 8 eight  en
 9 nine   en
 1 eins   ge
 2 zwei   ge
 3 drei   ge
 4 vier   ge
 5 fünf   ge
 6 sechs  ge
 7 sieben ge
 8 acht   ge
 9 neun   ge
;

proc sql;
  select
    val,
    nam
  into
    :val_3,
    :german_name_3
  from
    tq84_num_translation
  where
    lang eq 'en' and
    val between 3 and 7;
quit;


/* Note: only the first record (val = 3) is assigned to
   the macro variable! */
%put val_3 = &val_3;
%put german_name_3 = &german_name_3;

separated by

data tq84_num_translation;

  input
    val     1- 2
    nam  $  4- 9
    lang $ 11-12
  ;

datalines;
 1 one    en
 2 two    en
 3 three  en
 4 four   en
 5 five   en
 6 six    en
 7 seven  en
 8 eight  en
 9 nine   en
 1 eins   ge
 2 zwei   ge
 3 drei   ge
 4 vier   ge
 5 fünf   ge
 6 sechs  ge
 7 sieben ge
 8 acht   ge
 9 neun   ge
;

proc sql;
  select
    val,
    nam
  into
    :vals         seperated by ' ',
    :german_names separated by ','
  from
    tq84_num_translation
  where
    lang eq 'en' and
    val between 3 and 7;
quit;



%put Values: &vals;
%put German names: &german_names;

Macro variable

proc sql;
  select
    min(age),
    max(age)
  into
    :minAge,
    :maxAge
  from
     sashelp.class;
run;

%put Min age is &minAge, max age is &maxAge;

Through

data tq84_num_translation;

  input
    val     1- 2
    nam  $  4- 9
    lang $ 11-12
  ;

datalines;
 1 one    en
 2 two    en
 3 three  en
 4 four   en
 5 five   en
 6 six    en
 7 seven  en
 8 eight  en
 9 nine   en
 1 eins   ge
 2 zwei   ge
 3 drei   ge
 4 vier   ge
 5 fünf   ge
 6 sechs  ge
 7 sieben ge
 8 acht   ge
 9 neun   ge
;

proc sql;
  select
    val,
    nam
  into
    :val_3         through :val_7,
    :german_name_3 through :german_name_7
  from
    tq84_num_translation
  where
    lang eq 'en' and
    val between 3 and 7;
quit;

/* A macro variable is created for every record in the select statement */
%put vals: &val_3 &val_4 &val_5 &val_6 &val_7;
%put German names: &german_name_3 &german_name_4 &german_name_5 &german_name_6 &german_name_7;

No upper bound

data tq84_data;
  length num   4.
         txt  $3;

  input  num txt;

datalines;
18 foo
13 bar
5 foo
19 baz
22 baz
8 foo
5 bar
18 foo
13 bar
run;

proc sql;
  select
    sum(num),
    txt      
  into
  /* We know there are three records, so upper bound
     can be specified */
    :sum_1 - :sum_3,
    :txt_1 - :txt_3
  from
    tq84_data
  group by
    txt;

  %put &=txt_1 &=sum_1;
  %put &=txt_2 &=sum_2;
  %put &=txt_3 &=sum_3;
quit;


proc sql;
  select
    num,
    txt
  into
  /* We don't know number of selected rows. So,
     we specify an "open" upper bound: */
    :num1-,
    :txt1-
  from
    tq84_data;

quit;

%macro showSQLResult;
/* This macro iterates over the selected records.
   It uses the sqlobs automatic macro variable
   which was set in the select statement. */
   %do obs=1 %to &sqlobs;
       %put Observation &obs - &&txt&obs | &&num&obs;
   %end;
%mend;

%showSQLResult;

See also