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?
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
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.
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.
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.
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.
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.
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
Thanks data_null__. 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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.