02-19-2012 09:49 AM
I'm writing my first SAS program and I need some guidance on something very simple:
I need to read a dataset from an excel file with multiple sheets (but I need to read one sheet). Here's the code I came with that doesn't work sofar. I needed to print the first 20 observations
* Read data from external file into SAS data set;
sheet = "test";
INPUT OrdNum CustName CustAdd CustCd Item Price;
PROC PRINT DATA = Orders;
sheet = "test";
Any help is greatly appreciated.
02-19-2012 09:55 AM
You can't use the input method you've shown for importing a spreadsheet. You can either use proc import, assigning the spreadsheet to a libname, or what is called DDE.
You can accomplish the first one simply by clicking on file import, from the menu bar. If you want to see the code that is actually submitted, during the processing one of the questions the system will ask you is whether you want to save the program that was created.
02-19-2012 10:26 AM
The first two methods I suggested require the license of a product called SAS/Access for pcfiles. If you don't have that, then your alternatives are either to used DDE (which I wouldn't recommend for someone who is just learning SAS), an already developed macro that uses DDE in the background (which I also wouldn't recommend for someone who is just learning SAS),
or saving the spreadsheet (from within Excel) to a comma delimited file (csv) and then using code very similar to that which you originally were trying to use.
Again, in the last case, I'd use proc import and have it save the resulting program and then reviewing the resulting program.
02-19-2012 10:21 PM
I managed to get it to work using this code:
PROC IMPORT OUT= WORK.Orders DATAFILE="C:\MySAS\Orders.xls" DBMS=xls REPLACE;
PROC Contents DATA = Work.Orders;
Proc Print DATA = Work.Orders (obs=10);
But I have this question:
I need to create a new variable "NewPrice" to convert the "Price" into a binary variable and assign the value "1" if the "Price" >= 20, otherwise assign "0"
Here's my code that didn't work:
INPUT "NewPrice $";
If 'Price' < 20 then 'NewPrice' = 0;
else 'NewPrice' = 1;
02-19-2012 10:58 PM
You created a SAS file called orders. All you have to do, now, is modify that file:
if Price < 20 then NewPrice = 0;
else NewPrice = 1;