Data2datastep macro issue

[smackerz1988]

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;

[PaigeMiller]

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.

[smackerz1988]

But am I able to do this in the macro somewhere?. To C:\Users\ instead? 

[PaigeMiller]

Does not the argument FILE allow you to do this?

[smackerz1988]

I've tried but I still get the same authorisation error

[Reeza]

Show how you're calling the macro.

[Tom · Accepted solution]

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)

[smackerz1988]

Thanks @Tom . It was that and a small naming issue with the program it was being outputted to. Thanks both for your help!

[Tom]

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

[smackerz1988]

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.