DATA Step, Macro, Functions and more

Writing and reading SAS datasets via the log (or listing)

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 129
Accepted Solution

Writing and reading SAS datasets via the log (or listing)

I'm using a remote access SAS system and need to write a small SAS dataset to the log or listing file, and then read this output back into SAS on my local system with minimal manual editing. The data are (almost) all numeric.

 

One option is to use proc tabulate (or print or report), with appropriate form characters to give comma deliminated output. This gives a nice compact file which can be easily read in as csv, but gets messy when there are many variables and they spill past the 256 character limit.

 

Another might be to use 'put _all_'. A bit voluminous, but it can handle many variables. Is there any easy way to read this output back into a SAS dataset?

 

Any other suggestions?


Accepted Solutions
Solution
‎01-15-2016 06:28 PM
Respected Advisor
Posts: 3,777

Re: Writing and reading SAS datasets via the log (or listing)

[ Edited ]

I think this will work well enough.  The put records will flow over and the input will flowover to read them back.  It also provides the necessary code to read the data back into a SAS data set. 

 

Update I tested this with a LS less than the length of a long character variable and it does not read in correctly.  Make sure that LS is larger than the longest character variable.  There are other approaches we could try if long character variables are an issue.

 

data _null_;
   file log dsd ps=32767 ls=80 dlm=_dlm_;
   set sashelp.cars(obs=10) end=_eof_;
   if _n_ eq 1 then link define;
   put (_all_) (:);
   if _eof_ then put ';;;;' / 'Run;';
   return;
 define:
   _dlm_=' ';
   put 'Data transfered;';
   put +3 'infile datalines dsd;';
   length _name_ $32 _type_ $1 _length_ 8 _format_ $32;
   do while(1);
      call vnext(_name_,_type_,_length_);
      if lowcase(_name_) in('_dlm_' '_eof_') then continue;
      if lowcase(_name_) in('_eof_' '_name_') then leave;
      put +3 'Attrib ' _name_ 'length=' @;
      if _type_ eq 'C' then put '$' @;
      put _length_ @;
      _format_ = vformatx(_name_);
      if not missing(_format_) then put 'format=' _format_ 'informat=' _format_ @;
      put ';';
      end;
   put +3 'input (_all_)(:);';
   put +3 'Datalines4;';
   _dlm_=',';
   return;
   run;

 

The ouput looks like this for 10 records from SASHELP.CARS

Data transfered;
   infile datalines dsd;
   Attrib Make length=$13 format=$13. informat=$13. ;
   Attrib Model length=$40 format=$40. informat=$40. ;
   Attrib Type length=$8 format=$8. informat=$8. ;
   Attrib Origin length=$6 format=$6. informat=$6. ;
   Attrib DriveTrain length=$5 format=$5. informat=$5. ;
   Attrib MSRP length=8 format=DOLLAR8. informat=DOLLAR8. ;
   Attrib Invoice length=8 format=DOLLAR8. informat=DOLLAR8. ;
   Attrib EngineSize length=8 format=BEST12. informat=BEST12. ;
   Attrib Cylinders length=8 format=BEST12. informat=BEST12. ;
   Attrib Horsepower length=8 format=BEST12. informat=BEST12. ;
   Attrib MPG_City length=8 format=BEST12. informat=BEST12. ;
   Attrib MPG_Highway length=8 format=BEST12. informat=BEST12. ;
   Attrib Weight length=8 format=BEST12. informat=BEST12. ;
   Attrib Wheelbase length=8 format=BEST12. informat=BEST12. ;
   Attrib Length length=8 format=BEST12. informat=BEST12. ;
   input (_all_)(:);
   Datalines4;
Acura,MDX,SUV,Asia,All,"$36,945","$33,337",3.5,6,265,17,23,4451,106,189
Acura,RSX Type S 2dr,Sedan,Asia,Front,"$23,820","$21,761",2,4,200,24,31,2778,101
172
Acura,TSX 4dr,Sedan,Asia,Front,"$26,990","$24,647",2.4,4,200,22,29,3230,105,183
Acura,TL 4dr,Sedan,Asia,Front,"$33,195","$30,299",3.2,6,270,20,28,3575,108,186
Acura,3.5 RL 4dr,Sedan,Asia,Front,"$43,755","$39,014",3.5,6,225,18,24,3880,115
197
Acura,3.5 RL w/Navigation 4dr,Sedan,Asia,Front,"$46,100","$41,100",3.5,6,225,18
24,3893,115,197
Acura,NSX coupe 2dr manual S,Sports,Asia,Rear,"$89,765","$79,978",3.2,6,290,17
24,3153,100,174
Audi,A4 1.8T 4dr,Sedan,Europe,Front,"$25,940","$23,508",1.8,4,170,22,31,3252,104
179
Audi,A41.8T convertible 2dr,Sedan,Europe,Front,"$35,940","$32,506",1.8,4,170,23
30,3638,105,180
Audi,A4 3.0 4dr,Sedan,Europe,Front,"$31,840","$28,846",3,6,220,20,28,3462,104

 

 

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Writing and reading SAS datasets via the log (or listing)

When you say remote access, do you mean by remote desktop or similar? Not SAS/CONNECT?

Sounds a bit awkward to do a SAS report just for make easy to import it back SAS again, can yuou explain the requirement?

If you need to transfer data between SAS hosts, and don't have SAS/CONNECT licensed, and the hosts are on different OS, use PROC CPORT/CIMPORT.

If that's for some reason not feasible, use PROC EXPORT/data step to create a "clean" csv file, should be easier to import rather an tabulate etc.

Data never sleeps
Super User
Super User
Posts: 7,413

Re: Writing and reading SAS datasets via the log (or listing)

I agree with @LinusH, why not just write to a file?  You could create a CSV file, or just save the dataset output from the procedure with adding out=.  Or you could get the data and generate a datastep from the using datalines.  Numerous options, but the question really is why are you doing this.

Frequent Contributor
Posts: 129

Re: Writing and reading SAS datasets via the log (or listing)

Reason: basically for privacy. I'm sending a SAS program by email, it's interrogating a dataset, the dataset owners are checking the output for any privacy violations, then sending the output on to me. Their system is only setup to handle the log and listing outputs. 

Super User
Super User
Posts: 7,413

Re: Writing and reading SAS datasets via the log (or listing)

Sounds very odd.  Why would you not have one of the normal chains of data transfer:
- Data Transfer agreement, stating what data is to be sent and when.  They would then export said data and send to you.

- Access restricted rights, i.e. have access to the database, but a restricted portion of it.

 

Am not seein why you would be sending a program to them, I mean how do you know what to program if you dont have any access?  How so they know what to return to you.  Why can their system only handle (not sure if that is read in or out) log an listings - do you mean they can only handle text based file formats, if so then CSV would be number one option, followed by XML, JSON or one of the other text based data transfer formats.

Trusted Advisor
Posts: 2,113

Re: Writing and reading SAS datasets via the log (or listing)

RW9:  Bruce's challenge is fairly common with data enclaves, particularly for health care data.  The data owner sets the rules and you "play or go home". 

 

Bruce:  If you can create a .csv file on the remote system, then you could read it back in as a single variable per row of data and simply print it into your list file for transport back.

Solution
‎01-15-2016 06:28 PM
Respected Advisor
Posts: 3,777

Re: Writing and reading SAS datasets via the log (or listing)

[ Edited ]

I think this will work well enough.  The put records will flow over and the input will flowover to read them back.  It also provides the necessary code to read the data back into a SAS data set. 

 

Update I tested this with a LS less than the length of a long character variable and it does not read in correctly.  Make sure that LS is larger than the longest character variable.  There are other approaches we could try if long character variables are an issue.

 

data _null_;
   file log dsd ps=32767 ls=80 dlm=_dlm_;
   set sashelp.cars(obs=10) end=_eof_;
   if _n_ eq 1 then link define;
   put (_all_) (:);
   if _eof_ then put ';;;;' / 'Run;';
   return;
 define:
   _dlm_=' ';
   put 'Data transfered;';
   put +3 'infile datalines dsd;';
   length _name_ $32 _type_ $1 _length_ 8 _format_ $32;
   do while(1);
      call vnext(_name_,_type_,_length_);
      if lowcase(_name_) in('_dlm_' '_eof_') then continue;
      if lowcase(_name_) in('_eof_' '_name_') then leave;
      put +3 'Attrib ' _name_ 'length=' @;
      if _type_ eq 'C' then put '$' @;
      put _length_ @;
      _format_ = vformatx(_name_);
      if not missing(_format_) then put 'format=' _format_ 'informat=' _format_ @;
      put ';';
      end;
   put +3 'input (_all_)(:);';
   put +3 'Datalines4;';
   _dlm_=',';
   return;
   run;

 

The ouput looks like this for 10 records from SASHELP.CARS

Data transfered;
   infile datalines dsd;
   Attrib Make length=$13 format=$13. informat=$13. ;
   Attrib Model length=$40 format=$40. informat=$40. ;
   Attrib Type length=$8 format=$8. informat=$8. ;
   Attrib Origin length=$6 format=$6. informat=$6. ;
   Attrib DriveTrain length=$5 format=$5. informat=$5. ;
   Attrib MSRP length=8 format=DOLLAR8. informat=DOLLAR8. ;
   Attrib Invoice length=8 format=DOLLAR8. informat=DOLLAR8. ;
   Attrib EngineSize length=8 format=BEST12. informat=BEST12. ;
   Attrib Cylinders length=8 format=BEST12. informat=BEST12. ;
   Attrib Horsepower length=8 format=BEST12. informat=BEST12. ;
   Attrib MPG_City length=8 format=BEST12. informat=BEST12. ;
   Attrib MPG_Highway length=8 format=BEST12. informat=BEST12. ;
   Attrib Weight length=8 format=BEST12. informat=BEST12. ;
   Attrib Wheelbase length=8 format=BEST12. informat=BEST12. ;
   Attrib Length length=8 format=BEST12. informat=BEST12. ;
   input (_all_)(:);
   Datalines4;
Acura,MDX,SUV,Asia,All,"$36,945","$33,337",3.5,6,265,17,23,4451,106,189
Acura,RSX Type S 2dr,Sedan,Asia,Front,"$23,820","$21,761",2,4,200,24,31,2778,101
172
Acura,TSX 4dr,Sedan,Asia,Front,"$26,990","$24,647",2.4,4,200,22,29,3230,105,183
Acura,TL 4dr,Sedan,Asia,Front,"$33,195","$30,299",3.2,6,270,20,28,3575,108,186
Acura,3.5 RL 4dr,Sedan,Asia,Front,"$43,755","$39,014",3.5,6,225,18,24,3880,115
197
Acura,3.5 RL w/Navigation 4dr,Sedan,Asia,Front,"$46,100","$41,100",3.5,6,225,18
24,3893,115,197
Acura,NSX coupe 2dr manual S,Sports,Asia,Rear,"$89,765","$79,978",3.2,6,290,17
24,3153,100,174
Audi,A4 1.8T 4dr,Sedan,Europe,Front,"$25,940","$23,508",1.8,4,170,22,31,3252,104
179
Audi,A41.8T convertible 2dr,Sedan,Europe,Front,"$35,940","$32,506",1.8,4,170,23
30,3638,105,180
Audi,A4 3.0 4dr,Sedan,Europe,Front,"$31,840","$28,846",3,6,220,20,28,3462,104

 

 

Respected Advisor
Posts: 3,777

Re: Writing and reading SAS datasets via the log (or listing)

Seem like I also remember a SUG paper I think by SAS author about a program to do similar but with no data loss. Some decimal values will have loss of precision when the data are converted to character and back to numeric.
Frequent Contributor
Posts: 129

Re: Writing and reading SAS datasets via the log (or listing)

Thanks . That looks great - though I can see a few language features that I will have to study up on. I don't have any long character variables - so that won't be an issue. (I recognise that loss of precision will be an issue, so I will have to pay attention to my formats).

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 310 views
  • 2 likes
  • 5 in conversation