BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I am new to SAS.

What I want to do is to read in an xls file, then either update the existing xls file or create a new xls file. But it seems only created a new xls file, didn't update the records.

Here is my code:

filename input "replace.xls";
filename output "replaceout.xls";


data testdata;
infile input recfm=f ignoreDOSeof;
input ;
RegularExperssionId=prxparse('s/M[. ]/MA/');
call prxchange(RegularExperssionId, -1, input);
file output recfm=f;
put _infile_;
run;

Thanks for all your help. And do let me know if I post the message in the wrong group.
2 REPLIES 2
ArtC
Rhodochrosite | Level 12
The special variable _INFILE_ only contains the information from the input buffer. Although the information from the input buffer is written to the Program Data Vector, PDV, _INFILE_ does not receive any information from the program data vector. Modified variable values are contained in the PDV not in _INFILE_ as is shown here:
[pre}data a;
input x z$;
put _infile_;
put x=;
x = x+1;
put _infile_;
put x=;
datalines;
1 a
5 b
run;[/pre]

You need to write the values directly from the PDV not the input buffer.
dhana
Fluorite | Level 6
You can use PROCs to read and write to excel files. I have included an example code here for your reference.

PROC IMPORT OUT= WORK.excelout
DATAFILE= "D:\Finance\Analysis\Historydata.xls"
DBMS=EXCEL2000 REPLACE;
RANGE="RegDta$";
GETNAMES=YES;
RUN;

The first line tells the dataset to be created.The second lline tells the location of the excel file to be read.The third line tells SAS the type of DBMS, in this case it is excel file.The fourth line tells which work sheet needs to be selected from the excel file. First line of the work sheet will be used as variable names, this is mentioned in the fifth line.

Like this you can PROC EXPORT to export your SAS dataset to an excel file.

PROC EXPORT DATA= WORK.ESTIMATES
OUTFILE= "C:\Documents and Settings\carreir\Desktop\SASestimates.xls"
DBMS=EXCEL2000 REPLACE;
RUN;


Thanks

Dhanasekaran R
Allianz Cornhill Information Services
Trivandrum

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 748 views
  • 0 likes
  • 3 in conversation