Hello,
I'm using this version of the macro but I keep getting an error message when I try and run it on an intermediary dataset within a program
ERROR: Insufficient authorization to access C:\Windows\system32\create_WORK_CM_data.sas.
I know this related to a default path but where in the macro can I update it to redirect it
%macro data2datastep(dsn,lib,outlib,file,obs,fmt,lbl);
%local varlist fmtlist inputlist msgtype ;
%if %superq(obs)= %then %let obs=MAX;
%let msgtype=NOTE;
%if %superq(dsn)= %then %do;
%let msgtype=ERROR;
%put &msgtype: You must specify a data set name;
%put;
%goto syntax;
%end;
%let dsn=%qupcase(%superq(dsn));
%if %superq(dsn)=!HELP %then %do;
%syntax:
data _null_;
call symput ('LS',getoption('LS','startupvalue'));
run;
options ls=100;
%put &msgtype: &SYSMACRONAME macro help document:;
%put &msgtype- Purpose: Converts a data set to a SAS DATA step.;
%put &msgtype- Syntax: %nrstr(%%)&SYSMACRONAME(dsn<,lib,outlib,file,obs,fmt,lbl>);
%put &msgtype- dsn: Name of the dataset to be converted. Required.;
%put &msgtype- lib: LIBREF of the original dataset. (Optional - if DSN is not fully qualified);
%put &msgtype- outlib: LIBREF for the output dataset. (Optional - default is WORK);
%put &msgtype- file: Fully qualified filename for the DATA step code produced. (Optional);
%put &msgtype- Default is %nrstr(create_&outlib._&dsn._data.sas) in the SAS default directory.;
%put &msgtype- obs: Max observations to include the created dataset.;
%put &msgtype- (Optional) Default is MAX (all observations);
%put &msgtype- fmt: Format the numeric variables in the output dataset like the original data set? ;
%put &msgtype- (YES|NO - Optional) Default is YES;
%put &msgtype- lbl: Reproduce column labels in the output dataset? ;
%put &msgtype- (YES|NO - Optional) Default is YES;
%put;
%put NOTE: &SYSMACRONAME cannot be used in-line - it generates code.;
%put NOTE- Every FORMAT in the original data must have a corresponding INFORMAT of the same name.;
%put NOTE- Data set label is automatically re-created.;
%put NOTE- Only numeric column formats can be re-created, character column formats are ingnored.;
%put NOTE- Use !HELP to print these notes.;
options ls=&ls;
%return;
%end;
%if %superq(fmt)= %then %let fmt=YES;
%let fmt=%qupcase(&fmt);
%if %superq(lbl)= %then %let lbl=YES;
%let lbl=%qupcase(&lbl);
%if %superq(lib)= %then %do;
%let lib=%qscan(%superq(dsn),1,.);
%if %superq(lib) = %superq(dsn) %then %let lib=WORK;
%else %let dsn=%qscan(&dsn,2,.);
%end;
%if %superq(outlib)= %then %let outlib=WORK;
%let lib=%qupcase(%superq(lib));
%let dsn=%qupcase(%superq(dsn));
%if %sysfunc(exist(&lib..&dsn)) ne 1 %then %do;
%put ERROR: (&SYSMACRONAME) - Dataset &lib..&dsn does not exist.;
%let msgtype=NOTE;
%GoTo syntax;
%end;
%if %superq(file)= %then %do;
%let file=create_&outlib._&dsn._data.sas;
%if %symexist(USERDIR) %then %let file=&userdir/&file;
%end;
%if %symexist(USERDIR) %then %do;
%if %qscan(%superq(file),-1,/\)=%superq(file) %then
%let file=&userdir/&file;
%end;
proc sql noprint;
select Name
into :varlist separated by ' '
from dictionary.columns
where libname="&lib"
and memname="&dsn"
;
select case type
when 'num' then
case
when missing(format) then cats(Name,':32.')
else cats(Name,':',format)
end
else cats(Name,':$',length,'.')
end
into :inputlist separated by ' '
from dictionary.columns
where libname="&lib"
and memname="&dsn"
;
%if %qsubstr(%superq(lbl),1,1)=Y %then %do;
select strip(catx('=',Name,put(label,$quote.)))
into : lbllist separated by ' '
from dictionary.columns
where libname="&lib"
and memname="&dsn"
and label is not null
;
%end;
%else %let lbllist=;
select memlabel
into :memlabel trimmed
from dictionary.tables
where libname="&lib"
and memname="&dsn"
;
%if %qsubstr(%superq(fmt),1,1)=Y %then %do;
select strip(catx(' ',Name,format))
into :fmtlist separated by ' '
from dictionary.columns
where libname="&lib"
and memname="&dsn"
and format is not null
and format not like '$%'
;
%end;
%else %let fmtlist=;
quit;
%put _local_;
data _null_;
file "&file" dsd;
if _n_ =1 then do;
%if %superq(memlabel)= %then %do;
put "data &outlib..&dsn;";
%end;
%else %do;
put "data &outlib..&dsn(label=%tslit(%superq(memlabel)));";
%end;
put @3 "infile datalines dsd truncover;";
put @3 "input %superq(inputlist);";
%if not (%superq(fmtlist)=) %then %do;
put @3 "format %superq(fmtlist);";
%end;
%if not (%superq(lbllist)=) %then %do;
put @3 "label %superq(lbllist);";
%end;
put "datalines4;";
end;
set &lib..&dsn(obs=&obs) end=last;
put &varlist @;
if last then do;
put;
put ';;;;';
end;
else put;
run;
%mend;
This seems like the most likely place and I've tried altering it but no luck
%if %superq(file)= %then %do;
%let file=create_&outlib._&dsn._data.sas;
%if %symexist(USERDIR) %then %let file=&userdir/&file;
%end;
%if %symexist(USERDIR) %then %do;
%if %qscan(%superq(file),-1,/\)=%superq(file) %then
%let file=&userdir/&file;
%end;
Just tell it where you want to write the file when you call the macro by using the FILE parameter.
Looking at the code it appears it was the raw physical name of the file since it is adding quotes around it when it uses the value in the FILE statement.
data _null_;
file "&file" dsd;
So use something like:
%data2datastep(dsn=sashelp.class,file=c:\mydir\myfile.sas)
ERROR: Insufficient authorization to access C:\Windows\system32\create_WORK_CM_data.sas.
Unlikely that you are allowed to write to that folder on Windows. Pick a folder that you can write to.
But am I able to do this in the macro somewhere?. To C:\Users\ instead?
Does not the argument FILE allow you to do this?
I've tried but I still get the same authorisation error
Show how you're calling the macro.
Just tell it where you want to write the file when you call the macro by using the FILE parameter.
Looking at the code it appears it was the raw physical name of the file since it is adding quotes around it when it uses the value in the FILE statement.
data _null_;
file "&file" dsd;
So use something like:
%data2datastep(dsn=sashelp.class,file=c:\mydir\myfile.sas)
Thanks @Tom . It was that and a small naming issue with the program it was being outputted to. Thanks both for your help!
You could also use https://github.com/sasutils/macros/blob/master/ds2post.sas instead.
Might be easier since the default is to just dump the code into the SAS log and you can then just copy and paste it where ever you want.
2 filename fetch url "https://raw.githubusercontent.com/sasutils/macros/master/ds2post.sas"; 3 %include fetch ; 238 %ds2post(sashelp.class); NOTE: Table WORK._DS2POST_ created, with 5 rows and 8 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.08 seconds cpu time 0.04 seconds NOTE: The file _CODE_ is: (system-specific pathname), (system-specific file attributes) NOTE: 4 records were written to the file (system-specific pathname). The minimum record length was 11. The maximum record length was 46. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds NOTE: The file _CODE_ is: (system-specific pathname), (system-specific file attributes) NOTE: 19 records were written to the file (system-specific pathname). The minimum record length was 17. The maximum record length was 21. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: The file _CODE_ is: (system-specific pathname), (system-specific file attributes) NOTE: 1 record was written to the file (system-specific pathname). The minimum record length was 4. The maximum record length was 4. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: The infile _CODE_ is: (system-specific pathname), (system-specific file attributes) data work.class (label='Student Data'); infile datalines dsd dlm='|' truncover; input Name :$8. Sex :$1. Age Height Weight ; datalines4; Alfred|M|14|69|112.5 Alice|F|13|56.5|84 Barbara|F|13|65.3|98 Carol|F|14|62.8|102.5 Henry|M|14|63.5|102.5 James|M|12|57.3|83 Jane|F|12|59.8|84.5 Janet|F|15|62.5|112.5 Jeffrey|M|13|62.5|84 John|M|12|59|99.5 Joyce|F|11|51.3|50.5 Judy|F|14|64.3|90 Louise|F|12|56.3|77 Mary|F|15|66.5|112 Philip|M|16|72|150 Robert|M|12|64.8|128 Ronald|M|15|67|133 Thomas|M|11|57.5|85 William|M|15|66.5|112 ;;;; NOTE: 24 records were read from the infile (system-specific pathname). The minimum record length was 4. The maximum record length was 46. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Fileref _CODE_ has been deassigned. NOTE: Deleting WORK._DS2POST_ (memtype=DATA). NOTE: PROCEDURE DELETE used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
Interesting I wasn't aware of this but yes I see the upside of just copying and pasting from the log. I will stick with this for now but thanks for informing me of this alternative.
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.