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
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
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.
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
@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;
Mark, one word: brilliant
Bart
as always community is very much responsive and helpful . Thanks all for their valuable time and response
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.