update all columns of specific naming convention across monthly dataset

[LearnByMistk]

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

[maguiremq]

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.

[yabwon · Accepted solution]

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

[mkeintz]

---

@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;

[yabwon]

Mark, one word: brilliant

Bart

[LearnByMistk]

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