TAGATTR not work in data set

[bulewind]

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;

[SASKiwi]

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

[Cynthia_sas]

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]

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

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

[bulewind]

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]

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]

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]

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 · Accepted solution]

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]

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]

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]

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]

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]

---

@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]

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