topic Re: Exporting a Dataset to Datalines in SAS Programming

Exporting a Dataset to Datalines

DavidPhillips2 — Tue, 22 Mar 2016 19:57:06 GMT

Is there a good article on how to export a SAS dataset you have in a stored process to a datalines statement. I want to take a dataset I have in a stored process and share it in a forum post.

Re: Exporting a Dataset to Datalines

Reeza — Tue, 22 Mar 2016 21:07:58 GMT

Why yes there is 😀

http://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Also, if you're on EG there's a custom task that can be used - see Chris Hemedingers blog.

Re: Exporting a Dataset to Datalines

DavidPhillips2 — Wed, 23 Mar 2016 13:27:03 GMT

Which post is it in his blog?

General blog link:

http://blogs.sas.com/content/sasdummy/author/chrishemedinger/page/4/

Re: Exporting a Dataset to Datalines

Reeza — Wed, 23 Mar 2016 13:56:50 GMT

Here's a list to 11 of his custom task

http://blogs.sas.com/content/sasdummy/2013/11/25/11-custom-tasks/

And specifically EG custom task - Data to Data Step

http://blogs.sas.com/content/sasdummy/2013/01/21/turn-your-data-set-into-a-data-step-program/

Re: Exporting a Dataset to Datalines

DavidPhillips2 — Wed, 30 Mar 2016 17:31:21 GMT

Reeza,

When I tried to run the macro in the blog http://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/ I was unable to get it to work. How do you use this Marco? I copied it to Enterprise Guide and tried passing parameters.

Re: Exporting a Dataset to Datalines

Reeza — Wed, 30 Mar 2016 17:47:15 GMT

I wrote a post about how to do it using SAS UE

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

I'm assuming you have a dataset somewhere that you'd like to create the dataset for?

You would open the data2datastep.sas macro and run that.

Then call the macro, The third parameter is the name and path of the file that gets created. You'll have to specify that for your system.

Something like the following I imagine:

%data2datastep(cars,sashelp,C:\temp\out.sas,5)

It probably depends a bit on your set up, if you're on a server for instance it might be different.

Re: Exporting a Dataset to Datalines

DavidPhillips2 — Wed, 30 Mar 2016 17:56:02 GMT

I've got a server setup:

ERROR: Insufficient authorization to access /sasconfig/Lev1/SASApp/C:\temp\out.sas.

Re: Exporting a Dataset to Datalines

Reeza — Wed, 30 Mar 2016 18:03:13 GMT

Yeah, unfortunately you'll need a place to be able to create the file on the server. What happens if you leave the third parameter blank?

I'll take a look at the macro later and see if there's a way to output it to a dataset or log that could then be copied to avoid this issue.

I've only perused the code so far, so not sure if that's even possible.

I assume you weren't able to install the add in?

Re: Exporting a Dataset to Datalines

DavidPhillips2 — Wed, 30 Mar 2016 20:20:19 GMT

I found a location to save the output to that works for me.

Is the add on safe? It looks like an install file from an unknown vendor.

I’m trying to modify the code to allow me to output an active dataset so that I can narrow down the dataset before outputting it. But understanding the code is tricky.

If I could replace this section:

from dictionary.columns
where libname="&lib"
and memname="&dsn"

with something like:

from work.mytable

Re: Exporting a Dataset to Datalines

Reeza — Wed, 30 Mar 2016 20:24:38 GMT

What do you mean by narrow down the dataset?

Wouldn't you subset the table and then pass that to the macro?

data sub;

set have;

where <limit>;

run;

%data2datastep(sub, work, ,20);

The part of the code you're currently viewing would provide the column names and data types. I'm not sure that referencing your table would make sense.

The add on is safe, it's published by SAS, @ChrisHemedinger

I found that I needed IT Admin rights to unlock it though.

Re: Exporting a Dataset to Datalines

DavidPhillips2 — Wed, 30 Mar 2016 20:27:52 GMT

Reeza,

That works. I wasn't sure how to reference the work library like that.

Thanks for your help on this.

Re: Exporting a Dataset to Datalines

DavidPhillips2 — Wed, 30 Mar 2016 20:38:33 GMT

I put 10000 in for obs but my output is cut off at about 2000 rows. My dataset only has 4 columns so its not huge.

Re: Exporting a Dataset to Datalines

Reeza — Wed, 30 Mar 2016 20:45:14 GMT

There's no limit in the code that I can see that would restrict it to 2000 records...when you run the code, it only creates 2000 observations?

Re: Exporting a Dataset to Datalines

DavidPhillips2 — Wed, 30 Mar 2016 20:47:06 GMT

It creates 2321 records and cuts off on the last record. I think it is some sort of length issue.

I'm looking at how to install the plugin.

Re: Exporting a Dataset to Datalines

Reeza — Wed, 30 Mar 2016 20:51:31 GMT

This works for me. Does it truncate for you as well?

data random;
do i=1 to 10000;
x=1;
y=x*2;
z=rand('normal', 25, 5);
w=floor(y*z);
output;
end;
run;


%data2datastep(random,work,C:\_localdata\out.sas,10000)

Re: Exporting a Dataset to Datalines

DavidPhillips2 — Wed, 30 Mar 2016 20:59:02 GMT

That was not truncated for me. I wonder if its the data types.

Re: Exporting a Dataset to Datalines

DavidPhillips2 — Wed, 30 Mar 2016 21:10:53 GMT

I ran my data again with 20000 and it worked. I think it was just an odd one off.