topic How to use labels in proc export or create tab dlm file using proc print in ODS and Base Reporting

How to use labels in proc export or create tab dlm file using proc print

w020637 — Mon, 09 Mar 2009 19:06:30 GMT

I need to create a tab dlm file out of a sas dataset with label names.

So if I can achieve either of the following it would work.

a. I am able to get the labels using proc print but tab delimited is a problem.
b. I am able to create tab delimited using proc export but getting the labels is a problem.

Would request sas gurus to help me out.

Regards

Re: How to use labels in proc export or create tab dlm file using proc print

sbb — Mon, 09 Mar 2009 20:11:26 GMT

SAS-supplied macro %DS2CSV can generate such an output.

Scott Barry
SBBWorks, Inc.

Re: How to use labels in proc export or create tab dlm file using proc print

Cynthia_sas — Mon, 09 Mar 2009 20:42:03 GMT

Hi:
Also,
[pre]
ODS CSV FILE='xxxxxx.csv';
proc print data=sashelp.class label;
run;
ODS CSV CLOSE:
[/pre]

will do what you want.

cynthia

Re: How to use labels in proc export or create tab dlm file using proc print

sbb — Mon, 09 Mar 2009 20:51:17 GMT

I read the OP as wanting tab-delimited output, rather than comma-delimited. I looked briefly for some ODS CSV parameter option but found none.

Scott Barry
SBBWorks, Inc.

Re: How to use labels in proc export or create tab dlm file using proc print

w020637 — Mon, 09 Mar 2009 21:18:35 GMT

Yes, Scott, The objective is to get a tab delimited file.

Thanks

w020637

Re: How to use labels in proc export or create tab dlm file using proc print

Cynthia_sas — Tue, 10 Mar 2009 04:41:15 GMT

Sorry for the incomplete answer. There is in 9.2, a delimiter= option that allows you to change the delimiter from a comma to a tab character (which is 09'x, I believe) for ODS CSV.

If you invoke this way, you will see what the options are:
[pre]
ods csv file='c:\temp\csvhelp.csv'
options(doc='Help');
[/pre]

Then the delimiter option goes in parens:
[pre]
ods csv file='c:\temp\tabhelp.txt'
options(doc='Help' delimiter="09"x);
proc print data=sashelp.class(obs=3) label;
run;
ods csv close;
[/pre]

cynthia

Re: How to use labels in proc export or create tab dlm file using proc print

data_null__ — Thu, 14 Mar 2019 13:34:12 GMT

> There is in 9.2, a
> delimiter= option that allows you to change the
> delimiter from a comma to a tab character (which is
> 09'x, I believe) for ODS CSV.

That will be a nice feature. I look forward to using 9.2, soon perhaps.

The features of the FILE and PUT statement plus the call routine VNEXT make the data step solution fairly concise.

filename FT24F001 temp lrecl=512;
data _null_;
   file FT24F001 dsd dlm='09'x;
   set sashelp.shoes(obs=3);
   if _n_ eq 1 then link nameRow;
   put (_all_)(:);
   return;
 nameRow:
   length _LABEL_ $128 _NAME_ $32;
   if _n_ eq 1 then do;
      do while(1);
         call vnext(_name_);
         if _name_ eq '_LABEL_' then leave;
         _label_ = vlabelx(_name_);
         put _label_ @;
         end;
      put;
      end;
   return;
   run;


data _null_;
   infile FT24F001;
   input;
   list;
   run;

Re: How to use labels in proc export or create tab dlm file using proc print

w020637 — Tue, 10 Mar 2009 14:33:55 GMT

Hi guys,

I am using 9.1 and so the above solutions are not working for me. Appreciate the alacrity with which you guys have responded.

Regards

w020637

Re: How to use labels in proc export or create tab dlm file using proc print

Cynthia_sas — Tue, 10 Mar 2009 15:13:53 GMT

Hi:
I believe that you can download a copy of the CSV tagset template that will work for SAS 9.1.3 from this site:
http://support.sas.com/rnd/base/ods/odsmarkup/ (scroll down until you see the link for the CSV tagsets).

The SUGI paper that describes the OPTIONS suboption list and the DELIMITER suboption was written for SAS 9.1.3, so I believe you CAN get the delimiter, you just have to do some extra work because it's not included automatically with SAS (as it is in 9.2).
http://support.sas.com/rnd/base/ods/odsmarkup/p236-31.pdf

Although this note talks about installing and using the ExcelXP tagset template, the information about installing a tagset template and using the ODS PATH statement, also apply for updates to the CSV tagset template:
http://support.sas.com/kb/32/394.html

cynthia

Re: How to use labels in proc export or create tab dlm file using proc print

w020637 — Wed, 11 Mar 2009 13:55:24 GMT

http://ftp.sas.com/techsup/download/sample/datastep/robust.html

helps to get the requirement

Regards

w020637

Re: How to use labels in proc export or create tab dlm file using proc print

data_null__ — Wed, 11 Mar 2009 16:21:27 GMT

But my program does work in V9.1. You just assumed it would not work?

Re: How to use labels in proc export or create tab dlm file using proc print

ChrisNZ — Mon, 03 Aug 2009 00:47:33 GMT

Sorry for reviving this thread, I got pointed to it from another thread.
data _null_, I like the simplicity of your data step, and the way you got around enumerating variables.
Fyi, I made a small macro out of it, for those (frequent) cases when tweaking the output is not needed.
[pre]

%macro write_CSV(fileref= /* REQD: export fileref */
,dataset= /* REQD: dataset name, including options if necessary */
,delimiter=',' /* OPTN: eg: '09'x and ' ' for tab and space */
,firstrow=LABEL /* OPTN: Values: LABEL or NAME or NONE */
,addquotes=SPECIALCHAR /* OPTN: Values: ALLVAR or ALLCHAR or SPECIALCHAR */
);
data _null_;
set &dataset;
file &fileref dlm=&delimiter %if %upcase(&addquotes) ne ALLCHAR %then dsd;;
%if %upcase(&addquotes)=ALLCHAR %then format _CHARACTER_ $quote.;;
%if %upcase(&firstrow) ne NONE %then if _N_ = 1 then link firstrow;;
put (_ALL_)(%if %upcase(&addquotes)=ALLVAR %then~;:);
length __LABEL__ $256 __NAME__ $32;
return;
firstrow:
do while( __NAME__ ne '__LABEL__' );
call vnext(__NAME__);
__LABEL__ = vlabelx(__NAME__);
if __NAME__ ne '__LABEL__' then put __%upcase(&firstrow)__ @;
else put;
end;
run;
%mend;
[/pre]
One question though: how come all PDV variables are not output when put (_ALL_) is run?

Re: How to use labels in proc export or create tab dlm file using proc print

Andre — Mon, 03 Aug 2009 12:56:42 GMT

Chris
Your macro must be completed
as in the case of an ALLCHAR choice there is an error

[pre]
file &fileref dlm=&delimiter %if %upcase(&addquotes) ne ALLCHAR %then dsd%str(;);
%else %str(;);
[/pre]

Andre from Paris ( not linked to any submarine activity 😉 )

Re: How to use labels in proc export or create tab dlm file using proc print

ChrisNZ — Mon, 03 Aug 2009 21:09:51 GMT

Fixed, André, thank you.
I usually try to end my %if tests with a double ;; to remove %str() clutter, but I thought I'd post a more formal version... I have reverted to my old self now. That will teach me! 🙂

Do you know why some PDV variables are not output when put (_ALL_) is run? I am still perplexed.

Re: How to use labels in proc export or create tab dlm file using proc print

Cynthia_sas — Mon, 03 Aug 2009 22:14:59 GMT

Chris:
I ran a much simpler test and found that the simple PUT _ALL_ writes out everything, including _N_ and _ERROR_, as shown below. If you're seeing something else, you might want to work with Tech Support or redo your macro to see if you can figure out where things might be going wrong.

cynthia
[pre]
data _null_;
set sashelp.shoes(obs=5);
file 'c:\temp\test_putall.txt';
format _CHARACTER_ $quote.;
format _NUMERIC_;
if _N_ = 1 then link firstrow;
put _ALL_;
return;
firstrow:
put 'What will be written out?';
return;
run;

Output:
What will be written out?
Region="Africa" Product="Boot" Subsidiary="Addis Ababa" Stores=12 Sales=29761 Inventory=191821 Returns=769 _ERROR_=0 _N_=1
Region="Africa" Product="Men's Casual" Subsidiary="Addis Ababa" Stores=4 Sales=67242 Inventory=118036 Returns=2284 _ERROR_=0 _N_=2
Region="Africa" Product="Men's Dress" Subsidiary="Addis Ababa" Stores=7 Sales=76793 Inventory=136273 Returns=2433 _ERROR_=0 _N_=3
Region="Africa" Product="Sandal" Subsidiary="Addis Ababa" Stores=10 Sales=62819 Inventory=204284 Returns=1861 _ERROR_=0 _N_=4
Region="Africa" Product="Slipper" Subsidiary="Addis Ababa" Stores=14 Sales=68641 Inventory=279795 Returns=1771 _ERROR_=0 _N_=5

[/pre]

Re: How to use labels in proc export or create tab dlm file using proc print

ChrisNZ — Mon, 03 Aug 2009 22:49:29 GMT

Hi Cynthia,

Replacing
put _ALL_;
with
put (_ALL_)(:);

will drop all variables declared after the put statement, including _N_and _ERROR_ .

Re: How to use labels in proc export or create tab dlm file using proc print

Cynthia_sas — Mon, 03 Aug 2009 23:07:51 GMT

Hi:
I must not understand what you mean. When I replaced PUT _ALL_; with PUT (_ALL_) (:); this is what I expected and what I got:
[pre]
What will be written out?
"Africa" "Boot" "Addis Ababa" 12 29761 191821 769
"Africa" "Men's Casual" "Addis Ababa" 4 67242 118036 2284
"Africa" "Men's Dress" "Addis Ababa" 7 76793 136273 2433
"Africa" "Sandal" "Addis Ababa" 10 62819 204284 1861
"Africa" "Slipper" "Addis Ababa" 14 68641 279795 1771
[/pre]

Actually, I did -not- expect to see _N_ and _ERROR_ in the previous posting's output (the first time with the simple PUT _ALL_) because I thought those were only written to the LOG and not to an external file. When I added the dlm=',' option to the FILE statement, then this is what I got (again, without _N_ and _ERROR_ which don't belong in the file anyway, in my opinion):
[pre]
What will be written out? with dlm?
"Africa","Boot","Addis Ababa",12,29761,191821,769
"Africa","Men's Casual","Addis Ababa",4,67242,118036,2284
"Africa","Men's Dress","Addis Ababa",7,76793,136273,2433
"Africa","Sandal","Addis Ababa",10,62819,204284,1861
"Africa","Slipper","Addis Ababa",14,68641,279795,1771
[/pre]

Is your argument with _N_ and _ERROR_ not being in the output file?

cynthia

Re: How to use labels in proc export or create tab dlm file using proc print

ChrisNZ — Tue, 04 Aug 2009 00:26:13 GMT

>Is your argument with _N_ and _ERROR_ not being in the output file?
Not only, variable AAA is not output either.
Move the retain statement above the put statement and AAA will be output.

data _null_;
set sashelp.shoes(obs=5);
file 'c:\temp\test_putall.txt';
format _CHARACTER_ $quote.;
format _NUMERIC_;
if _N_ = 1 then link firstrow;
put (_ALL_)(:);
retain AAA 'kkk';
return;
firstrow:
put 'What will be written out?';
return;
run;

Re: How to use labels in proc export or create tab dlm file using proc print

ChrisNZ — Wed, 19 Aug 2009 03:32:40 GMT

Just pushing this one a last time to the forefront in hope of knowing why some PDV variables are not exported, and why the position of the retain statement matters here.

Re: How to use labels in proc export or create tab dlm file using proc print

Andre — Wed, 19 Aug 2009 09:06:22 GMT

Chris and Cynthia,

Indeed i was first surprise about this as aaa is existing in the pdv
the only difference of functionning is about the sequentionnal position of the retain statement : before or after the external put statement using the _ALL_

Is ther some secondary buffer existing for the tabular print generated by a put
and different from the pdv?
and filled or not following certain reasons?

to show this difference i used two different put in an ods datastep context!

in one case aaa is empty in the other it is filled!
so the _ALL_ has a special functionning in regard gathering information
depending theit context of use!

[pre]
data _null_;
put _all_;
set sashelp.shoes(obs=5);
file 'c:\temp\test_putall.txt';
format _CHARACTER_ $quote.;
format _NUMERIC_;
put (_ALL_)(:);
retain AAA 25;
run;
data _null_;
put _all_;
set sashelp.shoes(obs=5);
file 'c:\temp\test_putOK.txt';
format _CHARACTER_ $quote.;
format _NUMERIC_;
retain AAA 25;
put (_ALL_)(:);
run;
ods pdf file="c:\temp\odsput.pdf";
data _null_;
putlog _all_;
set sashelp.shoes(obs=5);
file print ods=(var=(_all_));
format _CHARACTER_ $quote.;
format _NUMERIC_;
put (_ALL_)(:);
retain AAA 25;
run;
ods pdf close;

ods pdf file="c:\temp\put_ods_.pdf";
data _null_;
putlog _all_;
set sashelp.shoes(obs=5);
file print ods=(var=(_all_));
format _CHARACTER_ $quote.;
format _NUMERIC_;
put (_ALL_)(:) _ODS_ ;
retain AAA 25;
run;
ods pdf close;
[/pre]

BUT Chris this is documented on

http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a000214163.htm

If you specify _ALL_, _CHAR_, or _NUMERIC_, only the variables that are defined before the RETAIN statement are affected.

HTH
Andre