BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BruceBrad
Lapis Lazuli | Level 10

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?

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

8 REPLIES 8
LinusH
Tourmaline | Level 20

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

BruceBrad
Lapis Lazuli | Level 10

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Doc_Duke
Rhodochrosite | Level 12

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.

data_null__
Jade | Level 19

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

 

 

data_null__
Jade | Level 19
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.
BruceBrad
Lapis Lazuli | Level 10

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).

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
  • 8 replies
  • 1167 views
  • 2 likes
  • 5 in conversation