Hi,
I have data that I copied and pasted from a website into an Excel spreadsheet in the attached file. Every 3 rows are actually 1 observation. For eg, the first row is: Brandname of the camera (Canon Powershot G12), the second row is the current price of that camera ($469.99) and the third row is the discount amount on that camera price ($30.00). Then there is blank row and then the next camera, and so on.
I need to read this data into SAS and create the 3 variables: Brandnamemodel, Price, discountamount. How could this be done?
I could not find any help with this online. Thank you so much and look forward to hearing from you.
Kelly
: I like your approach. To avoid missing the last record (as if saving it as is, the last empty line will be eliminated), and no need for junk: variables:
data want;
infile 'c:\temp\sampledatanov21.csv' truncover end=last;
informat brandname $50. price discount dollar8.2;
input brandname $&;
input @5 price;
input @5 discount;
if not last then input;
run;
Haikuo
Here is one way:
PROC IMPORT OUT= WORK.have
DATAFILE= "C:\art\sampledatanov21.xls"
DBMS=EXCEL REPLACE;
GETNAMES=no;
MIXED=NO;
RUN;
data want (drop=f:);
set have;
retain Brandnamemodel Price;
format Price Discount dollar8.2;
f2=find(f1,"$");
select (mod(_n_,4));
when (1) Brandnamemodel=strip(f1);
when (2) Price=input(substr(f1,f2+1),comma32.);
when (3) do;
Discount=input(substr(f1,f2+1),comma32.);
output;
end;
otherwise;
end;
run;
The first thing I would do is make sure the data pattern is actually the same. You have a different record format for
Canon A810 Red + 500 prints Web
If you fix that then 1) save as a CSV file as import from Excel is going to take somewhat more complicated code and
then
data want;
infile 'your path to saved file\sampledatanov21(1).csv' lrecl=256 pad missover ;
informat brandname $50. price discount dollar8.2 junk1 junk2 $4.;
input brandname $ 1-50;
input junk1 $ 1-4 price;
input junk2 $ 1-4 discount;
input;
drop junk:;
run;
Worked for me.
: I like your approach. To avoid missing the last record (as if saving it as is, the last empty line will be eliminated), and no need for junk: variables:
data want;
infile 'c:\temp\sampledatanov21.csv' truncover end=last;
informat brandname $50. price discount dollar8.2;
input brandname $&;
input @5 price;
input @5 discount;
if not last then input;
run;
Haikuo
The column pointers are better when I know what's going on. I wasn't sure if we might get something else in case the data does't follow the pattern in the first few lines of data. That's a habit I got into because I had way too many file layout descriptions not quite match the actual data received. The I don't drop the junk variables for diagnostics.
Hi Hai.kuo,
Thank you for the modified code. It worked perfectly and it was simple as well.
Since I am new to SAS could you please explain what $& means? I could not find it anywhere online.
Many thanks,
Kelly
Edward, thank you also for your help and input. Greatly appreciated!
Kelly
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.