BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kadz_sas1990
Obsidian | Level 7
there is no results produced for this sql step
proc sql;
create table colsToMask as
select c.libname, c.memname, c.name, c.type, c.length as len
from
(
select
upcase(scan(cats('WORK.',dsname),-2,'.')) as libname length=8,
upcase(scan(dsname,-1,'.')) as memname length=32,
upcase(colname) as name length=32
from excel
) e
,
dictionary.columns c
where
e.libname=c.libname
and e.memname=c.memname
and e.name=upcase(c.name)
and c.memtype='DATA'
order by c.libname, c.memname
;
quit;

five columns are created like libname,memname,name,type,len
but no entries are there.. can you please check this?
Patrick
Opal | Level 21

You define the tables and columns to change here:

 

data excel;
  infile datalines dsd;
  input dsname :$41. colname :$32.;
  datalines;
class,sex
class,age
class,name
sashelp.air,air
;

Column DSNAME contains either <libref>.<table name> or <table name> 

 

If you only pass in <table name> then the code will use WORK as the libref

Whatever you define here must actually exist (libref, table and column) without typo AND the library must be assigned. If it doesn't exist or the library is not assigned then the code won't fail but the SQL will not return any rows (as there was no match to what's in dictionary table dictionary.columns).

 

The SQL code and sample data for table EXCEL I've posted will pick-up SASHELP.AIR 

SASHELP.AIR is a SAS provided table that should exist in any SAS environment.

Capture.JPG

If you also execute the data step at the beginning in comment then the SQL will also pick-up WORK.CLASS

 

"what is the use of code gen file can you please explain your code."

The data _null_ step generates code using PUT statements writing this code to a file in WORK.

 

filename codegen temp;

 

Keyword TEMP in the filename statement makes this a temporary file in WORK.

 

Below statement then reads the generated code back into the SAS program so that it gets executed. The SOURCE2 option instructs SAS to write the %included code also to the SAS log.

%include codegen / source2;

With the code I've posted that's what you'll see in the SAS log.

105        %include codegen / source2;
NOTE: %INCLUDE (level 1) file CODEGEN is file <path and filename>
106       +data out.AIR ;
107       +  set SASHELP.AIR ;
108       +  AIR =9999;
109       +run;

And last but not least: You need to define a valid path for your environment to execute the code without error - or you use the libname definition in comment instead - but that's just for function testing and you shouldn't use it for your actual execution.

/* define library to store the tables with masked columns
   - do not overwrite the source tables
*/
%let outlib=out;
/*libname &outlib "%sysfunc(pathname(work))";*/
libname &outlib "<path name for output library"; 

 

Kadz_sas1990
Obsidian | Level 7
Brilliant Thanks a lot man.. working great.... 🙂
Kadz_sas1990
Obsidian | Level 7
@Patrick
and also the code is copying all the datasets in library(excel sheet) to output path, but I need to copy any one or two of the datasets in excel sheet, how it can be done. just if I specify the name of the dataset it should copy to output path
Patrick
Opal | Level 21

@Kadz_sas1990 wrote:
@Patrick
and also the code is copying all the datasets in library(excel sheet) to output path, but I need to copy any one or two of the datasets in excel sheet, how it can be done. just if I specify the name of the dataset it should copy to output path

Not sure that I understand your question.

The code will create a copy for any data set that you provide as control data via your excel sheet if/and only if at least one of the column names you provide actually exists in the source table.

It will not copy tables from a library if you don't provide the table name. - so just make sure that you don't have tables in your Excel sheet that you don't want copied.

Kadz_sas1990
Obsidian | Level 7
Actually i have a problem in the code, i have some dataset that has no sensitive columns so need there are no fields to be masked, if i give those dataset names alone in the excel it not copying the dataset since no masking column. can you please modify the code to copy the dataset if it dont have sensitive columns as well
Kadz_sas1990
Obsidian | Level 7
actually i need to copy all datasets using a single program, this program is not copying the datasets without masked column, if there is no masking column it should copy the dataset as it is. i.e. without masking any column it should simply use SET function.
Patrick
Opal | Level 21

@Kadz_sas1990 wrote:
actually i need to copy all datasets using a single program, this program is not copying the datasets without masked column, if there is no masking column it should copy the dataset as it is. i.e. without masking any column it should simply use SET function.
/*data class;*/
/*  set sashelp.class;*/
/*run;*/

data excel;
  infile datalines dsd truncover;
  input dsname :$41. colname :$32.;
  datalines;
class,sex
class,age
class,name
sashelp.air,air
sashelp.cars
sashelp.doesNotExist
;

proc sql;
  create table colsToMask as
  /* tables with matching col that actually exist in a currently assigned library */
  select c.libname, c.memname, c.name, c.type, c.length as len
  from
    (  
      select
        upcase(scan(cats('WORK.',dsname),-2,'.')) as libname length=8,
        upcase(scan(dsname,-1,'.')) as memname length=32,
        upcase(colname) as name length=32
      from excel
    ) e
    ,
    dictionary.columns c
   where 
    e.libname=c.libname 
    and e.memname=c.memname 
    and e.name=upcase(c.name)
    and c.memtype='DATA'

  union corr all
  /* tables with no matching col that actually exist in a currently assigned library */
  select e.libname, e.memname, e.name, ' ' as type length=4, . as len
  from
    (  
      select 
        upcase(scan(cats('WORK.',dsname),-2,'.')) as libname length=8,
        upcase(scan(dsname,-1,'.')) as memname length=32,
        upcase(colname) as name length=32
      from excel
    ) e
    ,
    dictionary.tables t
   where 
    e.libname=t.libname 
    and e.memname=t.memname 
    and t.memtype='DATA'
    and missing(e.name)

  order by libname, memname
  ;
quit;

/* define library to store the tables with masked columns
   - do not overwrite the source tables
*/
%let outlib=out;
libname &outlib "%sysfunc(pathname(work))";
/*libname &outlib "<path name for output library";*/

/* generate code for masking */
filename codegen temp;
data _null_;
/*  file print;*/
  file codegen;
  set colsToMask;
  by libname memname;
  if first.memname then
    do;
      put 
        "data &outlib.." memname ';' /
        '  set ' libname +(-1) '.' memname ';'
        ;
    end;
  if type='num' then 
    put '  ' name '=9999;'; 
  else 
  if type='char' then
    do;
      _replace=repeat('Z',min(len-1,10));
      put 
        @3 name '="' _replace +(-1)'" ;' 
        ; 
    end;
  if last.memname then
    put 'run;' ;
run;

/* ensure that there is always a codegen file */
data _null_;
  file codegen mod;
run;

/* execute code for masking */
%include codegen / source2;
Kadz_sas1990
Obsidian | Level 7

i am getting error:
119
120 /* execute code for masking */
121 %include codegen / source2;
NOTE: %INCLUDE (level 1) file CODEGEN is file W:\SASWORK\TD765_\#L70.
122 +data out.TEST_1 ;
123 + set INPUT.TEST_1 ;
124 + ="ZZZZZZZZZZZ" ;
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

125 +run;

Patrick
Opal | Level 21

@Kadz_sas1990 wrote:

i am getting error:
119
120 /* execute code for masking */
121 %include codegen / source2;
NOTE: %INCLUDE (level 1) file CODEGEN is file W:\SASWORK\TD765_\#L70.
122 +data out.TEST_1 ;
123 + set INPUT.TEST_1 ;
124 + ="ZZZZZZZZZZZ" ;
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

125 +run;


The code I've posted works for me.

Looking at the error it appears you haven't copied over the data _null_ step bit. There is also a change in there.

if type='num' then
put ' ' name '=9999;';
else
if type='char' then
do;

 

 

Kadz_sas1990
Obsidian | Level 7
added the line it working now Thanks..
Kadz_sas1990
Obsidian | Level 7

HI @Patrick,

 

Hope you are fine and safe,

 

in the program i changed some lines like below,

if type='num' then 
    put '  ' name '=9999;'; 
   else 
   if type='char' then
    do;
		put "   rl = 'TEST';";
		put @3 name  ' = rl||Strip(_N_);';	
    end; 
      if last.memname then 
    put 'run;' ;

in the above program it is creating a new column (rl) with records like "Test" in the final dataset,

 

why i changed code is i need character data to be masked with Test1,Test2,Test3....and so on for the field which is character datatype,

example: i am going to mask SURNAME field its Character datatype and my desired output is below,

Kadz_sas1990_0-1585633122434.png

 

But what is the problem is i am getting a new field 'rl' is added in the final dataset,

Kadz_sas1990_1-1585634082372.png

 

Could you please help me to change the code

Thanks

 

Patrick
Opal | Level 21

Something like below should work:

  if type='char' then
    do;
      if upcase(name)='SURNAME' then 
        do;
          put @3 name '= cats("TEST", _n_) ;'; 
        end;
      else
        do;
          _replace=repeat('Z',min(len-1,10));
          put 
            @3 name '="' _replace +(-1)'" ;' 
            ; 
        end;
    end;
Kadz_sas1990
Obsidian | Level 7
Works Good Thanks Patrick 🙂
Kadz_sas1990
Obsidian | Level 7

@Patrick 

Again i got a blocker here in some Columns which we are masking has no values or null, but the code is masking that value as well eg

obs

First name

Masked First name/ Current output

Desired output

1

John

Test1

Test1

2

Andrew

Test2

Test2

3

 

Test3

 

4

Milton

Test4

Test3

5

 

Test5

 

6

 

Test6

 

7

Mark

Test7

Test4

8

 

Test8

 

9

Rosy

Test9

Test5

 

Obs 3, 5, 6, 8 are blank values in the original dataset so it should be left blank in final dataset as well as given in last column of the above table.

i.e blank values in the column should be left as it is it should not be masked in both cases either it is character or number (both cases)

Can you please help on this.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 35 replies
  • 1626 views
  • 3 likes
  • 4 in conversation