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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: