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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.