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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.