DATA Step, Macro, Functions and more

Define variable from excel file

Reply
New Contributor
Posts: 3

Define variable from excel file

I'm wanting to define a variable based on a cell from an excel file. For example, if cell A1 in excel can be either Yes or No and in my SAS code I want to define a variable 'Toggle' to be equal to that cell.

Occasional Contributor
Posts: 13

Re: Define variable from excel file

Can you specify more about what you are doing?
PROC IMPORT
New Contributor
Posts: 3

Re: Define variable from excel file

Ultimately I'm trying to skip a piece of code if the value in excel = No, and execute the code if the value = Yes.
Valued Guide
Posts: 505

Re: Define variable from excel file

If the value of excel cell A2 is 'Alfred' then list the squares of the 1st ten integers

HAVE (SASHELP.CLASS in excel)
==============================

 d:/xls/class.xlsx

 +-------------------------+------+-----------+------------
 |      |    A      |   B  |   C  |    D      |    E      |
 +------+-----------+------+------+-----------+-----------+
 |      |           |      |      |           |           |
 |    1 |   NAME    |   AGE|  SEX |  HEIGHT   |  WEIGHT   |
 |    2 |   Alfred  |   14 |   M  |   55      |   96      |
 |    3 |   Alice   |   13 |   F  |   44      |   87      |
 |    4 |   Barbara |   13 |   F  |   48      |   88      |
 |    5 |   Carol   |   14 |   F  |   56      |   99      |
 |    6 |   Henry   |   14 |   M  |   52      |   84      |
 | ...  |   ...     |  ... | ...  |   ..      |   ...     |
 +------------------+------+------+-----------+-----------+

 [CLASS}


WANT ( If A2='Alfred then square numbers 1 to 10)
==================================================

Up to 40 obs WORK.WANT total obs=10

Obs      F1       I    I_SQUARE

  1    Alfred     1         1
  2    Alfred     2         4
  3    Alfred     3         9
  4    Alfred     4        16
  5    Alfred     5        25
  6    Alfred     6        36
  7    Alfred     7        49
  8    Alfred     8        64
  9    Alfred     9        81
 10    Alfred    10       100

WORKING CODE
===========+

      set xel.'class$A2:A2'n;
         or
      set xel.'[sheet1$}$A2:A2'n; (untested but I have used it)

FULL SOLUTION
=============

*                _                  _       _
 _ __ ___   __ _| | _____        __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|      \__,_|\__,_|\__\__,_|

;

libname xel "d:/xls/class.xlsx";
data xel.class;
  set sashelp.class;
run;quit;
libname xel clear;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

libname xel "d:/xls/class.xlsx" scan_text=no header=no;
data want;
   set xel.'class$A2:A2'n;
   if f1='Alfred' then
      do;
         do i=1 to 10;
            i_square=i*i;
            output;
         end;
      end;
   stop;
   ;
run;

Ask a Question
Discussion stats
  • 3 replies
  • 103 views
  • 0 likes
  • 3 in conversation