BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bulewind
Fluorite | Level 6

Could anybody tell me why TAGATTR not work in data set? I got error messages “ERROR 180-322: Statement is not valid or it is used out of proper order.” Thank you!

 

     data aaa(keep=a1 a2 a3);

     set bbb;

     a1=substr(readrec,2,4);

     a2=substr(readrec,6,8);

     var a3 / style={TAGATTR='format:text'};

     a3=substr(readrec,21,16);

     run;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, if you do use export procedure you will get very basic output, no fancy stuff and no formatting.  As for dbms I think you mean to put in there:

dbms=xlsx

 

Anyways, if you want nice Excel output for review, consider using ods tagsets.excelxp (or .excel) and use proc report.  You can then specify formatting, colors etc.

 

If its for data transfer, use CSV and forget Excel completely.

View solution in original post

14 REPLIES 14
SASKiwi
PROC Star

Your VAR statemet is only valid in PROC REPORT not a DATA step.

Cynthia_sas
SAS Super FREQ
There are several procedures that support the VAR statement: PROC PRINT and PROC MEANS are two of them. In PROC REPORT, you would have a DEFINE statement. In any case, within a DATA step program there is no VAR statement syntax and your specification would be better used within PROC PRINT, like this:
proc print data=yourdata;
var a1 a2;
var a3/style(data)={TAGATTR='format:text'};
run;

cynthia
Patrick
Opal | Level 21

Because var a3 / style={TAGATTR='format:text'}; is invalid SAS datastep syntax.

 

What are you trying to achieve with this line of code? 

bulewind
Fluorite | Level 6

Thank you, Patrick and Cynthia.

 

The reason I use TAGATTR on a3, because a3 is a number type but length is 16, when I export it to CSV file, it shows as scentific notation which I try to avoid. How could I do? Please help. a3/a2/a1 all substr from a long string which import from a CSV file. Please help, thanks!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Tagattr means tag attribute.  It is used when creating markup output such as HTML, XML, and adds in specific attiribute information for that tag.  It is nothing to do with Base SAS.

 

Now reading your question I was about to state that you should convert the value to character and then export that, however re-reading your post I think I see your problem: "when I export it to CSV file, it shows as scentific notation which I try to avoid" - what you mean here is that you export data to CSV (Comma Separated Variable text file) but when you open that in Excel (which has nothing to do with the CSV file format!) Excel is formatting the data in a way you don't want.  Correct.  This is a common Excel "feature" and one of the many reasons you shouldn't be using it for any purpose.  

With CSV as it contains no information other than variable name and data, there is no way of telling Excel on open to format that data in a certain way, you could write some VBA, or manually format it.  If you need Excel output then you would be better off using a procedure to arrive at Excel output - proc report with tagsets.excelxp for instance will allow you to put the tagattr in the proc report, but bear in mind that would then be XML output not CSV.

So what is the purpose of this?  If it is to send data to someone, stick with CSV and don't open it in Excel - as anyone who deals with data will be familiar with CSV and will know not to trust Excel.

If it is a report then go the proc report/tagsets report which gives you all the functionality of formatting and nice Excel output, but isn't great for data transfer.

 

Cynthia_sas
SAS Super FREQ
For a deeper explanation of using TAGATTR with PRINT, REPORT and TABULATE to bypass the Excel formatting problems, please refer to this paper:
https://support.sas.com/resources/papers/proceedings11/266-2011.pdf

cynthia
bulewind
Fluorite | Level 6

Thanks Cynthia and SASKiwi. Thank you RW9 - u got the point. The truth is I try to export to excel file but it doesn't work:

 

PROC EXPORT DATA=abc
OUTFILE="/abc_&sysdate._&systime..xlsx"
DBMS=EXCEL REPLACE;
PUTNAMES=YES;
RUN;

 

It is always said "ERROR: DBMS type EXCEL not valid for export." I am not sure why. Thanks.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, if you do use export procedure you will get very basic output, no fancy stuff and no formatting.  As for dbms I think you mean to put in there:

dbms=xlsx

 

Anyways, if you want nice Excel output for review, consider using ods tagsets.excelxp (or .excel) and use proc report.  You can then specify formatting, colors etc.

 

If its for data transfer, use CSV and forget Excel completely.

bulewind
Fluorite | Level 6

thank you, but if I use DBMS=xlsx, I got error message "ERROR: DBMS type XLSX not valid for export." Mine is SAS EG4.3, excel is 2010.

 

I try to use DBMS=xls, file got generated but when I open it by excel2010, got another error message - find unreadable content in excel file. Not sure why. Thanks for help.

bulewind
Fluorite | Level 6

 

btw, something interesting I have to share, actually I got one more error message "

"ERROR: An attachment record was truncated, LRECL is too small" when sending files as email attachment" , I fixed it by adding: 

content_type="application/vnd.ms-excel"

which is suggested officially : http://support.sas.com/kb/39/996.html

Excel file is generated and sent as attched but it is corrupted and I can't open it at all. Any idea about this? Thanks.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Maybe your SAS version is old, or perhaps EG is on Unix.  I don;t use EG so don't know offhand.  Here is reference form 9.2 version:

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003094743.htm

 

I would still recommend tagsets:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

 

Or CSV for data transfer.

bulewind
Fluorite | Level 6

Thanks RW9 for your suggestion, I finially use "Ods tagsets.ExcelXP" to generate an excel .xls file, and send it by attached email. I also remove "content_type="application/vnd.ms-excel" which I added before. It works. Only problem is when I try to open the excel file, it always gave me warning message "The file you are trying to open is in a different format than specified by the file extension. Verify that file is not corrupted and is from a trusted source before opening the file." Actually I opened it, it works well. My SAS is EG 4.3 on Unix, my excel on local machine is excel 2010. Thanks.

ballardw
Super User

@bulewind wrote:

Thanks RW9 for your suggestion, I finially use "Ods tagsets.ExcelXP" to generate an excel .xls file, and send it by attached email. I also remove "content_type="application/vnd.ms-excel" which I added before. It works. Only problem is when I try to open the excel file, it always gave me warning message "The file you are trying to open is in a different format than specified by the file extension. Verify that file is not corrupted and is from a trusted source before opening the file." Actually I opened it, it works well. My SAS is EG 4.3 on Unix, my excel on local machine is excel 2010. Thanks.


Tagsets.ExcelXP generates XML content that Excel will accept. You get that message because it is NOT xls and Microsoft is telling you that. I name my results as XML files and have Excel associated to open them by default.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its fine.  As you call the file XLSX Excel expects the file to be in the Open Office format (ZIP with subfolders/XML files), but what it reads its plain XML text file.  Hence it is letting you know that its not as expected.  You can live with it, or call your file .xml (although you would need to associate .xml with Excel otherwise it will open with your browser most likely).  

You can read about this:

https://support.microsoft.com/en-gb/kb/948615

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!

How to Concatenate Values

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.

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
  • 14 replies
  • 2397 views
  • 4 likes
  • 6 in conversation