Hello,
I have 5 columns with values as 0 or 1.
Id Col1 Col2 Col3 Col4 Col5
1 1 0 0 1 0
2 1 0 0 1 0
3 0 1 0 1 0
4 1 0 0 1 0
5 0 0 0 0 1
I am running a big program every week,the issue is for some week, one of the column have no data, hence that particular column itself is missing. For my report, I need that column, so would like to add a dummy column( missing column lets say col5) and assign values as 0. I tried Proc sql with case statement at the end of the final dataset which is not working.
Thanks for checking.
The harder part is setting to 0, adding the column is relatively trivial. Make a table with the structure you want and append it to that with 0 obs from the master table.
If you can have missing in you data this makes this a bit more complex, but if you don't, you can set any missing to 0 and be done.
proc sql;
create table table_format
(ID char(8),
Col1 num,
Col2 num,
Col3 num,
Col4 num,
Col5 num);
quit;
proc sql;
create table myData
(ID char(8),
Col1 num,
Col2 num,
Col3 num,
Col5 num);
insert into myData
values ('1', 1, 0, 0, 1)
values ('2', 1, 0, 1, 0);
quit;
data want;
set table_format(Obs=0) myData;
run;
Better would be to show an example of the input file with your problem and then what the desired output for that file would look like.
For instance, I am not sure when you say " one of the column have no data" whether that means the value of a variable is missing for all rows or data or only for a few.
What do you want if two variables are showing this behavior? or three?
Thanks ! You are right. This behavior can happen to 2 or more variables.
What I meant no data means, the value of the variable is missing for all rows from the prior step. I can't able to show the data in detail.
The harder part is setting to 0, adding the column is relatively trivial. Make a table with the structure you want and append it to that with 0 obs from the master table.
If you can have missing in you data this makes this a bit more complex, but if you don't, you can set any missing to 0 and be done.
proc sql;
create table table_format
(ID char(8),
Col1 num,
Col2 num,
Col3 num,
Col4 num,
Col5 num);
quit;
proc sql;
create table myData
(ID char(8),
Col1 num,
Col2 num,
Col3 num,
Col5 num);
insert into myData
values ('1', 1, 0, 0, 1)
values ('2', 1, 0, 1, 0);
quit;
data want;
set table_format(Obs=0) myData;
run;
Thank you.. It worked. Setting up 0 was easy for me.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: