I don't see how using Excel is going to help at all. It will probably just make the problem harder. And that is not even including the problem you got with trying to read the result.
What exactly is wrong with the file? Embedded commas are not a problem in a properly formatted CSV file. The values with commas do have quotes around them right?
If only some of the lines are messed up then run a simple data step to split the file into good and bad lines. Then you can probably just fix the bad line in a TEXT EDITOR and put the file back to together.
For example if the issue is that the person that generated the file did not add quotes around the commas you will end up with some lines that do not have the right number of fields on them. If the lines are short enough (32k bytes) you can use the _INFILE_ automatic variable.
So for example if you expected 20 fields per line you might split it like this:
data _null_;
infile 'original.csv' ;
input;
if countw(_infile_,',','qm') = 20 then file 'good.csv';
else file 'bad.csv';
put _infile_;
run;
Then fix the BAD lines, making fixed.csv.
Then read the two files together:
filename both ('good.csv','fixed.csv');
data want;
infile both dsd truncover firstobs=2;
length var1 $10 var2 8 ..... var20 8 ;
input var1 -- var20;
run;
... View more
I would just use Excel to convert the XLSX file to a CSV file (or multiple CSV files if it has multiple sheets) and then write a data step to read that. Make sure to eliminate any linefeed characters that might appear in any of the cells in the XLSX file first as they can cause SAS to think the observation has ended because the LINE has ended.
... View more
Use the reverse of the INFILE trick. Call it the FILE trick.
filename dummy temp;
data want;
set sashelp.class;
length line $100;
file dummy dsd dlm='|' ;
put @1 100*' ' @1 name--weight @;
line=_file_;
run;
... View more
@EdCenter wrote: Heh, so the wrinkle is that I have different tables with different columns that I have to interleave to create a single pseudo-EDI file for processing. So for simplicity, I have 4 tables: Header, Member, Detail, Trailer table. Each with different columns. I need to interleave it like this: Header Member(1) Detail(1) ... Member(n) Detail(n) Trailer What I'm trying to do with CATX is to concat everything into a single field, then interleave the records in each table (in the right order) into a flatfile.
This looks like basic report writing. Use BY group processing;
data _null_;
file out dsd ;
set have end=eof;
by member ;
if _n_=1 then put header;
if first.member then put member;
put details;
if eof then put trailer;
run;
Example:
128 data _null_;
129 set class end=eof;
130 by sex;
131 file log dsd dlm='|';
132 if _n_=1 then put 'Report of SASHELP.CLASS by SEX';
133 if first.sex then put sex;
134 put name -- weight;
135 if eof then put 'End of Report';
136 run;
Report of SASHELP.CLASS by SEX
F
Alice|F|13|56.5|84
Barbara|F|13|65.3|98
Carol|F|14|62.8|102.5
Jane|F|12|59.8|84.5
Janet|F|15|62.5|112.5
Joyce|F|11|51.3|50.5
Judy|F|14|64.3|90
Louise|F|12|56.3|77
Mary|F|15|66.5|112
M
Alfred|M|14|69|112.5
Henry|M|14|63.5|102.5
James|M|12|57.3|83
Jeffrey|M|13|62.5|84
John|M|12|59|99.5
Philip|M|16|72|150
Robert|M|12|64.8|128
Ronald|M|15|67|133
Thomas|M|11|57.5|85
William|M|15|66.5|112
End of Report
... View more
So the problem is the SECOND macro is generate COMPUTE and ENDCOMP statement when you don't want those statements.
Look at your non-macro code. The second block of repeated code is all inside of a COMPUTE block already.
... View more
You cannot do it for LABELS. You will need to modify the data first and use the new variable in the IDLABEL statement.
You could do it for NAMES. But that uses the ID statement not the IDLABEL statement.
... View more
If you have a new question start a new topic.
But you cannot read the full file using on 32k lines. There is at least one line that is longer then that.
51 data _null_;
52 infile xx lrecl=10000000;
53 input;
54 run;
NOTE: The infile XX is:
(system-specific pathname),
(system-specific file attributes)
NOTE: 57368 records were read from the infile (system-specific pathname).
The minimum record length was 0.
The maximum record length was 44181.
NOTE: DATA statement used (Total process time):
real time 1.48 seconds
cpu time 1.48 seconds
... View more
@GeorgeBonanza wrote:
Thanks so much. This is a very creative solution. Is this essentially what proc import is doing behind the scenes when importing a sheet or named range from Excel? If so, is there a way to modify the code so that it reads the XML from code run on a SAS server similar to how I'd modify a proc import by adding serveruser=; serverpass=; server=; port=; ?
No.
You will need to have access to the file directly.
What you are talking about is using SAS PC FILES server to access the file. That does not even work with the XLSX engine. I think that only works using the Windows supplied tools that the EXCEL engine uses.
... View more
So read it from the XML.
1 data test;
2 infile 'c:\downloads\tables.xlsx' zip member='xl/tables/table1.xml' recfm=n dsd dlm=' ';
3 input @ 'ref=' ref :$50.;
4 call symputx('ref',ref);
5 stop;
6 run;
NOTE: The infile 'c:\downloads\tables.xlsx' is:
(system-specific pathname),
(system-specific file attributes)
NOTE: 1 record was read from the infile (system-specific pathname).
NOTE: The data set WORK.TEST has 0 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
7
8 %put &=ref;
REF=A2:C4
... View more
So they made a named range that does not include the header row?
You can use the actual cell names instead.
proc import
dbms=xlsx
file='myfile.xlsx'
out=want replace
;
range='mysheet$A12:B27';
run;
But unless you examine the individual XML files in the XLSX file (or in your case it looks like you have an XLSM file) it will be hard to programmatically determine where to find the name row.
... View more
You have two problems.
One is at the planning phase:
If you have 3 input binary variables (anxiety, mood , eating) you have 8 possible combinations.
You seem to be trying to map those into just 4 different values.
One is at the execution phase:
Your logic is independent. So if EATING=1 then it does not matter what values the other two variables has because that last IF/THEN will set the value to 3.
Make yourself a truth table. For every possible combination of the three variables what do you want for the output variable?
Here is what your current code does:
Obs anxiety mood eating anymental
1 0 0 0 0
2 0 0 1 3
3 0 1 0 2
4 0 1 1 3
5 1 0 0 1
6 1 0 1 3
7 1 1 0 2
8 1 1 1 3
So what did you want to do? Did you just want to COUNT them? Is so the add them up. You could use the SUM() function.
anymental=sum(anxiety,mood,eating);
Result
Obs anxiety mood eating anymental
1 0 0 0 0
2 0 0 1 1
3 0 1 0 1
4 0 1 1 2
5 1 0 0 1
6 1 0 1 2
7 1 1 0 2
8 1 1 1 3
... View more
They must have done something wrong that messed up the validity of the XML files in the XLSX file.
The sensitivity labels my company makes us add to XLSX files does not cause trouble when reading the file with SAS.
NOTE: The SAS System stopped processing this step because of errors.
720 libname x xlsx 'c:\downloads\16digits.xlsx';
NOTE: Libref X was successfully assigned as follows:
Engine: XLSX
Physical Name: c:\downloads\16digits.xlsx
721 proc copy inlib=x outlib=work; run;
NOTE: Copying X.XXX to WORK.XXX (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: The import data set has 1 observations and 3 variables.
NOTE: There were 1 observations read from the data set X.XXX.
NOTE: The data set WORK.XXX has 1 observations and 3 variables.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
... View more
Why are you using two totally different syntaxes for the INSERT statement?
What happens if you use the second syntax in SAS?
libname myora oracle ...... ;
proc sql;
INSERT INTO myora.Customer_t (customerid, customername, customeraddress, customercity, customerstate, customerpostalcode)
VALUES (100, 'Cool Future', '123 Lake Side Drive', 'Ithaca', 'NY', '14850')
;
run;
... View more
