topic Re: update all columns of specific naming convention across monthly dataset in SAS Programming

update all columns of specific naming convention across monthly dataset

LearnByMistk — Wed, 03 Mar 2021 16:43:40 GMT

I' m having history score files which are monthly timestamp each having somewhere 20M records and 300 features( columns) . there are 200+ such scores files in which I have update same column as null . for example:

Scorefile_20200101,Scorefile_20200201...Scorefile_20201201 etc

Scorefile_202001 contains: (* id is unique)

id | var1 | var2 | var3 | var4 |..var300

1 |1 | 2.2 | 0.5 | 0.5 .....1.23

...

1000 |21 | 0.2 | 0.6 | 0.15 .....0.23

Now I want to update these vars with null value

code currently using sashelp.vcolumn to pull dataset name and column name

proc sql;

update &scorewidefile;

set &var;

run;

This query takes around 6 min per iteration as files are big with 20Million records and 300 columns.

Any alternate approach to expedite the process will be good to have.

Thanks in advance

Re: update all columns of specific naming convention across monthly dataset

maguiremq — Wed, 03 Mar 2021 17:40:55 GMT

What variable are you trying to update? Is it the one with the (...) prefix? Please post an example using the DATALINES statement so we can see the exact issue.

Re: update all columns of specific naming convention across monthly dataset

yabwon — Wed, 03 Mar 2021 18:01:24 GMT

I this is what you try to achieve:

data have;
infile cards dlm="|";
input id  var1  var2  var3   var4 var300;
cards;
1  |1      |  2.2   | 0.5    | 0.5  | 1.23
1000  |21      |  0.2   | 0.6    | 0.15   | 0.23
;
run;
proc print;
run;

data want;
  set have;
  call missing(of var:);
run;
proc print;
run;

Bart

Re: update all columns of specific naming convention across monthly dataset

mkeintz — Wed, 03 Mar 2021 18:10:31 GMT

@yabwon wrote:

I this is what you try to achieve:

data have;
infile cards dlm="|";
input id  var1  var2  var3   var4 var300;
cards;
1  |1      |  2.2   | 0.5    | 0.5  | 1.23
1000  |21      |  0.2   | 0.6    | 0.15   | 0.23
;
run;
proc print;
run;

data want;
  set have;
  call missing(of var:);
run;
proc print;
run;

Bart

You can even avoid invoking the call missing for each and every observation, yet still have all the variables (except ID) set to missing in the data set:

data want;
  if 0 then set have;
  set have (keep=id);
run;

Re: update all columns of specific naming convention across monthly dataset

yabwon — Wed, 03 Mar 2021 18:22:25 GMT

Mark, one word: brilliant

Bart

Re: update all columns of specific naming convention across monthly dataset

LearnByMistk — Sun, 07 Mar 2021 11:24:11 GMT

as always community is very much responsive and helpful . Thanks all for their valuable time and response