DATA Step, Macro, Functions and more

Changing variable data from . to Zeros

Reply
Occasional Contributor
Posts: 19

Changing variable data from . to Zeros

I have a dataset that SAS is adding . form missing data, I run a procedure If X = . then X = 0; 

 

However, when I do a proc transpose I loose the 0 and they are back to .

 

How can a get my final data to out put zeros instead of . for missing data.

 

data Cold;
set raw temp;
Response = 3;
if Resp = . then Resp = 0;
run;

 

proc transpose data=Cold out=ColdStor prefix = Item_;
by Stortype x_sum ID Year Month;
var Resp;
id ItemCode;
run;

 

PROC Star
Posts: 307

Re: Changing variable data from . to Zeros

I doubt that SAS is converting the zeros into missing ("."). What is likely happening is that you have values for Item_Code that are not present in every combination of "Stortype x_sum ID Year Month" in your by statement. For those cases, there is no value available to transpose, so you end up with "." To fix this, you'll need to do the replacement of "." with "0" after the transpose.

Occasional Contributor
Posts: 19

Re: Changing variable data from . to Zeros

Posted in reply to collinelliot

Do you have sample code?

Super User
Super User
Posts: 7,942

Re: Changing variable data from . to Zeros

Post example test data in the form of a datastep, we cannot see what you are working on.  SAS will not change 0's to missing, it will populate data that is missing, either through calculation or missing data to .

Occasional Contributor
Posts: 19

Re: Changing variable data from . to Zeros

ID    X     Y   Z

1      5     8   7

2      0     0   4

3      5     0   7

 

 I end up with:

 

ID    X     Y   Z

1      5     8   7

2      .       .   4

3      5     .    7

PROC Star
Posts: 307

Re: Changing variable data from . to Zeros

Without actual data and code, I don't see how you'd go from the first example to the data with missing values.

 

However, if you have a series of variables and you want to elminate the missing values, you can just do a simple array on them:

 

data want;
set have;
array fixme{*} x y z;
do i = 1 to dim(fixme);
fixme(i) = coalesce(fixme(i), 0);
/* OR: if fixme(i) = . then fixme(i) = 0; */
end;
run;

Occasional Contributor
Posts: 19

Re: Changing variable data from . to Zeros

Posted in reply to collinelliot

\2017\3\31\000\55999999999\01\01\\A\1\501\0\\
\2017\3\31\000\55999999999\01\01\\A\1\506\0\\
\2017\3\31\000\55999999999\01\01\\A\1\701\0\\
\2017\3\31\000\55999999999\01\01\\A\1\515\0\\
\2017\3\31\000\55999999999\01\01\\A\1\516\0\\

 input Year 4-5 Month Day Time X_sum 16-17 ID 18-26 T S Status$ Stortype ItemCode Resp;

 

I read in this data:

 

I do a proc transpose:

 

proc transpose data=Cold out=Stor prefix = Item_;
by Stortype X_sum ID Year Month;
var Resp;
id ItemCode;
run;

 

I get . for missing values

 

I need all missing values to be replaced with zeros not . to read into an antequated software that I do not control the coding for.

 

 

Super User
Super User
Posts: 7,942

Re: Changing variable data from . to Zeros

Why not post the code you are actually using and the data you are using as a datastep.  Its not the best use of our time to decode what you are doing and how from the tiny snippets of information you are posting.  For example if you run this code you will have a table with one row and 0's.

data cold;
  infile datalines dlm="\";
  input Year 4-5 Month Day Time X_sum 16-17 ID 18-26 T S Status$ Stortype ItemCode Resp;
datalines;
\2017\3\31\000\55999999999\01\01\\A\1\501\0\\
\2017\3\31\000\55999999999\01\01\\A\1\506\0\\
\2017\3\31\000\55999999999\01\01\\A\1\701\0\\
\2017\3\31\000\55999999999\01\01\\A\1\515\0\\
\2017\3\31\000\55999999999\01\01\\A\1\516\0\\
;
run;
proc transpose data=Cold out=Stor prefix = Item_;
  by Stortype X_sum ID Year Month;
  var Resp;
  id ItemCode;
run;
Occasional Contributor
Posts: 19

Re: Changing variable data from . to Zeros

I copied and pasted code that I am using directly from SAS and the data from the datafile.  I am not sure what else you are asking me to show or do.  I am very basic in my SAS programing knowledge, I have taught myself.

 

 

Occasional Contributor
Posts: 19

Re: Changing variable data from . to Zeros

This is the entire data file:  was just pasting a snippet earlier:

 

\2017\3\31\000\19300400690\01\01\\A\1\742\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\742\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\5000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\5000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\786\5000000\\
\2017\3\31\000\19300400690\01\01\\A\1\786\5000000\\
\2017\3\31\000\19300400690\01\01\\A\1\749\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\749\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\1000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\1000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\756\2500000\\
\2017\3\31\000\19300400690\01\01\\A\1\756\2500000\\
\2017\3\31\000\19300400690\01\01\\A\1\772\3000000\\
\2017\3\31\000\19300400690\01\01\\A\1\772\3000000\\
\2017\3\31\000\19300400690\01\01\\A\1\7000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\7000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\3000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\3000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\2000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\2000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\220\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\220\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\228\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\228\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\241\0\\
\2017\3\31\000\19300400690\01\01\\A\1\241\0\\
\2017\3\31\000\19300400690\01\01\\A\1\242\0\\
\2017\3\31\000\19300400690\01\01\\A\1\242\0\\
\2017\3\31\000\19300400690\01\01\\A\1\240\0\\
\2017\3\31\000\19300400690\01\01\\A\1\240\0\\
\2017\3\31\000\19300400690\01\01\\A\1\248\20000\\
\2017\3\31\000\19300400690\01\01\\A\1\248\20000\\
\2017\3\31\000\19300400690\01\01\\A\1\246\50000\\
\2017\3\31\000\19300400690\01\01\\A\1\246\50000\\
\2017\3\31\000\19300400690\01\01\\A\1\247\25000\\
\2017\3\31\000\19300400690\01\01\\A\1\247\25000\\
\2017\3\31\000\19300400690\01\01\\A\1\324\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\324\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\320\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\320\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400690\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400690\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9902\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9902\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9912\\5\
\2017\3\31\000\19300400690\01\01\\A\1\9912\\5\
\2017\3\31\000\19300400700\01\01\\A\1\501\151555\\
\2017\3\31\000\19300400700\01\01\\A\1\501\151555\\
\2017\3\31\000\19300400700\01\01\\A\1\506\0\\
\2017\3\31\000\19300400700\01\01\\A\1\506\0\\
\2017\3\31\000\19300400700\01\01\\A\1\512\0\\
\2017\3\31\000\19300400700\01\01\\A\1\512\0\\
\2017\3\31\000\19300400700\01\01\\A\1\517\0\\
\2017\3\31\000\19300400700\01\01\\A\1\517\0\\
\2017\3\31\000\19300400700\01\01\\A\1\523\0\\
\2017\3\31\000\19300400700\01\01\\A\1\523\0\\
\2017\3\31\000\19300400700\01\01\\A\1\538\0\\
\2017\3\31\000\19300400700\01\01\\A\1\538\0\\
\2017\3\31\000\19300400700\01\01\\A\1\536\0\\
\2017\3\31\000\19300400700\01\01\\A\1\536\0\\
\2017\3\31\000\19300400700\01\01\\A\1\542\0\\
\2017\3\31\000\19300400700\01\01\\A\1\542\0\\
\2017\3\31\000\19300400700\01\01\\A\1\537\0\\
\2017\3\31\000\19300400700\01\01\\A\1\537\0\\
\2017\3\31\000\19300400700\01\01\\A\1\5000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\5000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\547\25975\\
\2017\3\31\000\19300400700\01\01\\A\1\547\25975\\
\2017\3\31\000\19300400700\01\01\\A\1\1000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\1000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\610\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\610\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\591\0\\
\2017\3\31\000\19300400700\01\01\\A\1\591\0\\
\2017\3\31\000\19300400700\01\01\\A\1\553\96700\\
\2017\3\31\000\19300400700\01\01\\A\1\553\96700\\
\2017\3\31\000\19300400700\01\01\\A\1\155\20251\\
\2017\3\31\000\19300400700\01\01\\A\1\155\20251\\
\2017\3\31\000\19300400700\01\01\\A\1\145\20000\\
\2017\3\31\000\19300400700\01\01\\A\1\145\20000\\
\2017\3\31\000\19300400700\01\01\\A\1\563\0\\
\2017\3\31\000\19300400700\01\01\\A\1\563\0\\
\2017\3\31\000\19300400700\01\01\\A\1\556\0\\
\2017\3\31\000\19300400700\01\01\\A\1\556\0\\
\2017\3\31\000\19300400700\01\01\\A\1\578\0\\
\2017\3\31\000\19300400700\01\01\\A\1\578\0\\
\2017\3\31\000\19300400700\01\01\\A\1\576\500\\
\2017\3\31\000\19300400700\01\01\\A\1\576\500\\
\2017\3\31\000\19300400700\01\01\\A\1\577\500\\
\2017\3\31\000\19300400700\01\01\\A\1\577\500\\
\2017\3\31\000\19300400700\01\01\\A\1\571\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\571\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\7000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\7000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\3000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\3000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\2000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\2000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400700\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400700\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400700\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400700\01\01\\A\1\9902\1\\
\2017\3\31\000\19300400700\01\01\\A\1\9902\1\\
\2017\3\31\000\27300414340\01\01\\A\1\5000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\1000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\7000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\811\3500000\\
\2017\3\31\000\27300414340\01\01\\A\1\813\506600\\
\2017\3\31\000\27300414340\01\01\\A\1\818\4000000\\
\2017\3\31\000\27300414340\01\01\\A\1\815\3555100\\
\2017\3\31\000\27300414340\01\01\\A\1\3000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\426\3000000\\
\2017\3\31\000\27300414340\01\01\\A\1\425\3000000\\
\2017\3\31\000\27300414340\01\01\\A\1\427\2000000\\
\2017\3\31\000\27300414340\01\01\\A\1\429\2000000\\
\2017\3\31\000\27300414340\01\01\\A\1\432\50000000\\
\2017\3\31\000\27300414340\01\01\\A\1\2000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\851\0\\
\2017\3\31\000\27300414340\01\01\\A\1\8000\1\\
\2017\3\31\000\27300414340\01\01\\A\1\825\55400000\\
\2017\3\31\000\27300414340\01\01\\A\1\833\500000\\
\2017\3\31\000\27300414340\01\01\\A\1\4000\1\\
\2017\3\31\000\27300414340\01\01\\A\1\447\1500000\\
\2017\3\31\000\27300414340\01\01\\A\1\841\0\\
\2017\3\31\000\27300414340\01\01\\A\1\9910\032917\\
\2017\3\31\000\27300414340\01\01\\A\1\9904\5\\
\2017\3\31\000\27300414340\01\01\\A\1\9903\5\\
\2017\3\31\000\27300414340\01\01\\A\1\9901\1\\
\2017\3\31\000\27300414340\01\01\\A\1\9902\1\\
\2017\3\31\000\55300628500\01\01\\A\1\5000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\1000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\7000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\3000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\2000\1\\
\2017\3\31\000\55300628500\01\01\\A\1\220\20200\\
\2017\3\31\000\55300628500\01\01\\A\1\245\660000\\
\2017\3\31\000\55300628500\01\01\\A\1\246\5000000\\
\2017\3\31\000\55300628500\01\01\\A\1\247\2500000\\
\2017\3\31\000\55300628500\01\01\\A\1\8000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\4000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\9910\032917\\
\2017\3\31\000\55300628500\01\01\\A\1\9904\5\\
\2017\3\31\000\55300628500\01\01\\A\1\9903\5\\
\2017\3\31\000\55300628500\01\01\\A\1\9901\1\\
\2017\3\31\000\55300628500\01\01\\A\1\9902\1\\

 

data temp;
infile 'F:\STORAGE\Temp.dat' dlm='\';
input Year 4-5 Month Day Time X_sum 16-17 ID 18-26 T S Status$ Stortype ItemCode Resp;
run;

 

proc sort data=Temp nodup;
by X_sum ID ItemCode;
run;

 

proc transpose data=Temp out=Stor prefix = Item_;
by Stortype Fips_sum Poid Year Month;
var Resp;
id ItemCode;
run;

Super User
Posts: 19,782

Re: Changing variable data from . to Zeros

This error is due to missing records in your data, as in lines not existing.

 

When you do the TRANSPOSE you don't have records for all of your column headings so SAS converts these values to missing rather than 0.

 

You will need to correct it afterwards and set them to 0. SInce you've used a prefix that's hopefully the only variable with this prefix in your data step you can use an array with the short cut colon to reference all variables.

 

array myvars(*) temp_:;

do i=1 to dim(myvars);
if myvars(i) = . then myvars(i) = 0;
end;

 

 

PROC Star
Posts: 307

Re: Changing variable data from . to Zeros

A couple of issues that make it hard to address your questions:

 

1. Your sort does not sort variables in the order required by your transpose.

2. Your transpose uses by variables that are not in your data (Fips_sum Poid).

 

Can you supply the code that will run but does not generate the results you expect?

 

Occasional Contributor
Posts: 19

Re: Changing variable data from . to Zeros

Posted in reply to collinelliot

I apologize... I have to change variable names to post on here for security reasons:

 

 This is the entire data file:  was just pasting a snippet earlier:

 

\2017\3\31\000\19300400690\01\01\\A\1\742\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\742\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\5000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\5000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\786\5000000\\
\2017\3\31\000\19300400690\01\01\\A\1\786\5000000\\
\2017\3\31\000\19300400690\01\01\\A\1\749\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\749\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\1000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\1000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\756\2500000\\
\2017\3\31\000\19300400690\01\01\\A\1\756\2500000\\
\2017\3\31\000\19300400690\01\01\\A\1\772\3000000\\
\2017\3\31\000\19300400690\01\01\\A\1\772\3000000\\
\2017\3\31\000\19300400690\01\01\\A\1\7000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\7000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\3000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\3000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\2000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\2000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\220\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\220\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\228\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\228\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\241\0\\
\2017\3\31\000\19300400690\01\01\\A\1\241\0\\
\2017\3\31\000\19300400690\01\01\\A\1\242\0\\
\2017\3\31\000\19300400690\01\01\\A\1\242\0\\
\2017\3\31\000\19300400690\01\01\\A\1\240\0\\
\2017\3\31\000\19300400690\01\01\\A\1\240\0\\
\2017\3\31\000\19300400690\01\01\\A\1\248\20000\\
\2017\3\31\000\19300400690\01\01\\A\1\248\20000\\
\2017\3\31\000\19300400690\01\01\\A\1\246\50000\\
\2017\3\31\000\19300400690\01\01\\A\1\246\50000\\
\2017\3\31\000\19300400690\01\01\\A\1\247\25000\\
\2017\3\31\000\19300400690\01\01\\A\1\247\25000\\
\2017\3\31\000\19300400690\01\01\\A\1\324\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\324\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\320\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\320\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400690\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400690\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9902\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9902\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9912\\5\
\2017\3\31\000\19300400690\01\01\\A\1\9912\\5\
\2017\3\31\000\19300400700\01\01\\A\1\501\151555\\
\2017\3\31\000\19300400700\01\01\\A\1\501\151555\\
\2017\3\31\000\19300400700\01\01\\A\1\506\0\\
\2017\3\31\000\19300400700\01\01\\A\1\506\0\\
\2017\3\31\000\19300400700\01\01\\A\1\512\0\\
\2017\3\31\000\19300400700\01\01\\A\1\512\0\\
\2017\3\31\000\19300400700\01\01\\A\1\517\0\\
\2017\3\31\000\19300400700\01\01\\A\1\517\0\\
\2017\3\31\000\19300400700\01\01\\A\1\523\0\\
\2017\3\31\000\19300400700\01\01\\A\1\523\0\\
\2017\3\31\000\19300400700\01\01\\A\1\538\0\\
\2017\3\31\000\19300400700\01\01\\A\1\538\0\\
\2017\3\31\000\19300400700\01\01\\A\1\536\0\\
\2017\3\31\000\19300400700\01\01\\A\1\536\0\\
\2017\3\31\000\19300400700\01\01\\A\1\542\0\\
\2017\3\31\000\19300400700\01\01\\A\1\542\0\\
\2017\3\31\000\19300400700\01\01\\A\1\537\0\\
\2017\3\31\000\19300400700\01\01\\A\1\537\0\\
\2017\3\31\000\19300400700\01\01\\A\1\5000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\5000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\547\25975\\
\2017\3\31\000\19300400700\01\01\\A\1\547\25975\\
\2017\3\31\000\19300400700\01\01\\A\1\1000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\1000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\610\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\610\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\591\0\\
\2017\3\31\000\19300400700\01\01\\A\1\591\0\\
\2017\3\31\000\19300400700\01\01\\A\1\553\96700\\
\2017\3\31\000\19300400700\01\01\\A\1\553\96700\\
\2017\3\31\000\19300400700\01\01\\A\1\155\20251\\
\2017\3\31\000\19300400700\01\01\\A\1\155\20251\\
\2017\3\31\000\19300400700\01\01\\A\1\145\20000\\
\2017\3\31\000\19300400700\01\01\\A\1\145\20000\\
\2017\3\31\000\19300400700\01\01\\A\1\563\0\\
\2017\3\31\000\19300400700\01\01\\A\1\563\0\\
\2017\3\31\000\19300400700\01\01\\A\1\556\0\\
\2017\3\31\000\19300400700\01\01\\A\1\556\0\\
\2017\3\31\000\19300400700\01\01\\A\1\578\0\\
\2017\3\31\000\19300400700\01\01\\A\1\578\0\\
\2017\3\31\000\19300400700\01\01\\A\1\576\500\\
\2017\3\31\000\19300400700\01\01\\A\1\576\500\\
\2017\3\31\000\19300400700\01\01\\A\1\577\500\\
\2017\3\31\000\19300400700\01\01\\A\1\577\500\\
\2017\3\31\000\19300400700\01\01\\A\1\571\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\571\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\7000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\7000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\3000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\3000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\2000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\2000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400700\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400700\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400700\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400700\01\01\\A\1\9902\1\\
\2017\3\31\000\19300400700\01\01\\A\1\9902\1\\
\2017\3\31\000\27300414340\01\01\\A\1\5000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\1000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\7000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\811\3500000\\
\2017\3\31\000\27300414340\01\01\\A\1\813\506600\\
\2017\3\31\000\27300414340\01\01\\A\1\818\4000000\\
\2017\3\31\000\27300414340\01\01\\A\1\815\3555100\\
\2017\3\31\000\27300414340\01\01\\A\1\3000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\426\3000000\\
\2017\3\31\000\27300414340\01\01\\A\1\425\3000000\\
\2017\3\31\000\27300414340\01\01\\A\1\427\2000000\\
\2017\3\31\000\27300414340\01\01\\A\1\429\2000000\\
\2017\3\31\000\27300414340\01\01\\A\1\432\50000000\\
\2017\3\31\000\27300414340\01\01\\A\1\2000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\851\0\\
\2017\3\31\000\27300414340\01\01\\A\1\8000\1\\
\2017\3\31\000\27300414340\01\01\\A\1\825\55400000\\
\2017\3\31\000\27300414340\01\01\\A\1\833\500000\\
\2017\3\31\000\27300414340\01\01\\A\1\4000\1\\
\2017\3\31\000\27300414340\01\01\\A\1\447\1500000\\
\2017\3\31\000\27300414340\01\01\\A\1\841\0\\
\2017\3\31\000\27300414340\01\01\\A\1\9910\032917\\
\2017\3\31\000\27300414340\01\01\\A\1\9904\5\\
\2017\3\31\000\27300414340\01\01\\A\1\9903\5\\
\2017\3\31\000\27300414340\01\01\\A\1\9901\1\\
\2017\3\31\000\27300414340\01\01\\A\1\9902\1\\
\2017\3\31\000\55300628500\01\01\\A\1\5000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\1000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\7000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\3000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\2000\1\\
\2017\3\31\000\55300628500\01\01\\A\1\220\20200\\
\2017\3\31\000\55300628500\01\01\\A\1\245\660000\\
\2017\3\31\000\55300628500\01\01\\A\1\246\5000000\\
\2017\3\31\000\55300628500\01\01\\A\1\247\2500000\\
\2017\3\31\000\55300628500\01\01\\A\1\8000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\4000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\9910\032917\\
\2017\3\31\000\55300628500\01\01\\A\1\9904\5\\
\2017\3\31\000\55300628500\01\01\\A\1\9903\5\\
\2017\3\31\000\55300628500\01\01\\A\1\9901\1\\
\2017\3\31\000\55300628500\01\01\\A\1\9902\1\\

 

data temp;
infile 'F:\STORAGE\Temp.dat' dlm='\';
input Year 4-5 Month Day Time X_sum 16-17 ID 18-26 T S Status$ Stortype ItemCode Resp;
run;

 

proc sort data=Temp nodup;
by X_sum ID ItemCode;
run;

 

proc transpose data=Temp out=Stor prefix = Item_;
by Stortype X_sum ID Year Month;
var Resp;
id ItemCode;
run;

Super User
Posts: 11,343

Re: Changing variable data from . to Zeros

Here's how to post code:

data temp;
   infile datalines dlm='\';
   input Year 4-5 Month Day Time X_sum 16-17 ID 18-26 T S Status$ Stortype ItemCode Resp;
datalines;
\2017\3\31\000\19300400690\01\01\\A\1\742\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\742\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\5000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\5000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\786\5000000\\
\2017\3\31\000\19300400690\01\01\\A\1\786\5000000\\
\2017\3\31\000\19300400690\01\01\\A\1\749\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\749\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\1000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\1000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\756\2500000\\
\2017\3\31\000\19300400690\01\01\\A\1\756\2500000\\
\2017\3\31\000\19300400690\01\01\\A\1\772\3000000\\
\2017\3\31\000\19300400690\01\01\\A\1\772\3000000\\
\2017\3\31\000\19300400690\01\01\\A\1\7000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\7000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\3000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\3000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\2000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\2000\1\\
\2017\3\31\000\19300400690\01\01\\A\1\220\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\220\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\228\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\228\500000\\
\2017\3\31\000\19300400690\01\01\\A\1\241\0\\
\2017\3\31\000\19300400690\01\01\\A\1\241\0\\
\2017\3\31\000\19300400690\01\01\\A\1\242\0\\
\2017\3\31\000\19300400690\01\01\\A\1\242\0\\
\2017\3\31\000\19300400690\01\01\\A\1\240\0\\
\2017\3\31\000\19300400690\01\01\\A\1\240\0\\
\2017\3\31\000\19300400690\01\01\\A\1\248\20000\\
\2017\3\31\000\19300400690\01\01\\A\1\248\20000\\
\2017\3\31\000\19300400690\01\01\\A\1\246\50000\\
\2017\3\31\000\19300400690\01\01\\A\1\246\50000\\
\2017\3\31\000\19300400690\01\01\\A\1\247\25000\\
\2017\3\31\000\19300400690\01\01\\A\1\247\25000\\
\2017\3\31\000\19300400690\01\01\\A\1\324\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\324\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\320\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\320\250000\\
\2017\3\31\000\19300400690\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400690\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400690\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400690\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400690\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9902\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9902\1\\
\2017\3\31\000\19300400690\01\01\\A\1\9912\\5\
\2017\3\31\000\19300400690\01\01\\A\1\9912\\5\
\2017\3\31\000\19300400700\01\01\\A\1\501\151555\\
\2017\3\31\000\19300400700\01\01\\A\1\501\151555\\
\2017\3\31\000\19300400700\01\01\\A\1\506\0\\
\2017\3\31\000\19300400700\01\01\\A\1\506\0\\
\2017\3\31\000\19300400700\01\01\\A\1\512\0\\
\2017\3\31\000\19300400700\01\01\\A\1\512\0\\
\2017\3\31\000\19300400700\01\01\\A\1\517\0\\
\2017\3\31\000\19300400700\01\01\\A\1\517\0\\
\2017\3\31\000\19300400700\01\01\\A\1\523\0\\
\2017\3\31\000\19300400700\01\01\\A\1\523\0\\
\2017\3\31\000\19300400700\01\01\\A\1\538\0\\
\2017\3\31\000\19300400700\01\01\\A\1\538\0\\
\2017\3\31\000\19300400700\01\01\\A\1\536\0\\
\2017\3\31\000\19300400700\01\01\\A\1\536\0\\
\2017\3\31\000\19300400700\01\01\\A\1\542\0\\
\2017\3\31\000\19300400700\01\01\\A\1\542\0\\
\2017\3\31\000\19300400700\01\01\\A\1\537\0\\
\2017\3\31\000\19300400700\01\01\\A\1\537\0\\
\2017\3\31\000\19300400700\01\01\\A\1\5000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\5000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\547\25975\\
\2017\3\31\000\19300400700\01\01\\A\1\547\25975\\
\2017\3\31\000\19300400700\01\01\\A\1\1000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\1000\1\\
\2017\3\31\000\19300400700\01\01\\A\1\610\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\610\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\591\0\\
\2017\3\31\000\19300400700\01\01\\A\1\591\0\\
\2017\3\31\000\19300400700\01\01\\A\1\553\96700\\
\2017\3\31\000\19300400700\01\01\\A\1\553\96700\\
\2017\3\31\000\19300400700\01\01\\A\1\155\20251\\
\2017\3\31\000\19300400700\01\01\\A\1\155\20251\\
\2017\3\31\000\19300400700\01\01\\A\1\145\20000\\
\2017\3\31\000\19300400700\01\01\\A\1\145\20000\\
\2017\3\31\000\19300400700\01\01\\A\1\563\0\\
\2017\3\31\000\19300400700\01\01\\A\1\563\0\\
\2017\3\31\000\19300400700\01\01\\A\1\556\0\\
\2017\3\31\000\19300400700\01\01\\A\1\556\0\\
\2017\3\31\000\19300400700\01\01\\A\1\578\0\\
\2017\3\31\000\19300400700\01\01\\A\1\578\0\\
\2017\3\31\000\19300400700\01\01\\A\1\576\500\\
\2017\3\31\000\19300400700\01\01\\A\1\576\500\\
\2017\3\31\000\19300400700\01\01\\A\1\577\500\\
\2017\3\31\000\19300400700\01\01\\A\1\577\500\\
\2017\3\31\000\19300400700\01\01\\A\1\571\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\571\50000\\
\2017\3\31\000\19300400700\01\01\\A\1\7000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\7000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\3000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\3000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\2000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\2000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\8000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\4000\3\\
\2017\3\31\000\19300400700\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400700\01\01\\A\1\9910\032917\\
\2017\3\31\000\19300400700\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9904\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9903\5\\
\2017\3\31\000\19300400700\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400700\01\01\\A\1\9901\1\\
\2017\3\31\000\19300400700\01\01\\A\1\9902\1\\
\2017\3\31\000\19300400700\01\01\\A\1\9902\1\\
\2017\3\31\000\27300414340\01\01\\A\1\5000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\1000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\7000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\811\3500000\\
\2017\3\31\000\27300414340\01\01\\A\1\813\506600\\
\2017\3\31\000\27300414340\01\01\\A\1\818\4000000\\
\2017\3\31\000\27300414340\01\01\\A\1\815\3555100\\
\2017\3\31\000\27300414340\01\01\\A\1\3000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\426\3000000\\
\2017\3\31\000\27300414340\01\01\\A\1\425\3000000\\
\2017\3\31\000\27300414340\01\01\\A\1\427\2000000\\
\2017\3\31\000\27300414340\01\01\\A\1\429\2000000\\
\2017\3\31\000\27300414340\01\01\\A\1\432\50000000\\
\2017\3\31\000\27300414340\01\01\\A\1\2000\3\\
\2017\3\31\000\27300414340\01\01\\A\1\851\0\\
\2017\3\31\000\27300414340\01\01\\A\1\8000\1\\
\2017\3\31\000\27300414340\01\01\\A\1\825\55400000\\
\2017\3\31\000\27300414340\01\01\\A\1\833\500000\\
\2017\3\31\000\27300414340\01\01\\A\1\4000\1\\
\2017\3\31\000\27300414340\01\01\\A\1\447\1500000\\
\2017\3\31\000\27300414340\01\01\\A\1\841\0\\
\2017\3\31\000\27300414340\01\01\\A\1\9910\032917\\
\2017\3\31\000\27300414340\01\01\\A\1\9904\5\\
\2017\3\31\000\27300414340\01\01\\A\1\9903\5\\
\2017\3\31\000\27300414340\01\01\\A\1\9901\1\\
\2017\3\31\000\27300414340\01\01\\A\1\9902\1\\
\2017\3\31\000\55300628500\01\01\\A\1\5000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\1000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\7000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\3000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\2000\1\\
\2017\3\31\000\55300628500\01\01\\A\1\220\20200\\
\2017\3\31\000\55300628500\01\01\\A\1\245\660000\\
\2017\3\31\000\55300628500\01\01\\A\1\246\5000000\\
\2017\3\31\000\55300628500\01\01\\A\1\247\2500000\\
\2017\3\31\000\55300628500\01\01\\A\1\8000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\4000\3\\
\2017\3\31\000\55300628500\01\01\\A\1\9910\032917\\
\2017\3\31\000\55300628500\01\01\\A\1\9904\5\\
\2017\3\31\000\55300628500\01\01\\A\1\9903\5\\
\2017\3\31\000\55300628500\01\01\\A\1\9901\1\\
\2017\3\31\000\55300628500\01\01\\A\1\9902\1\\
;
run;

proc sort data=Temp nodup;
   by X_sum ID ItemCode;
run;

proc transpose data=Temp out=Stor prefix = Item_;
   by Stortype X_sum ID Year Month;
   var Resp;
   id ItemCode;
run;

Solutions may be:

 

data want;
   set stor;
   array it Item: ;
   do i = 1 to dim(it);
      if it[i] = . then it[i]=0;
   end;
   drop i;
run;

proc tabulate data=temp;
   class stortype X_sum ID Year Month itemcode;
   var resp;
   table stortype* X_sum* ID* Year *Month ,
         itemcode*resp=''*sum='' *f=best9.
         /misstext='0'
   ;
run;

the first sets the missing to 0.

 

Or if you are wanting to generate a report perhaps the proc tabulate will give you clues on where to start without the sort and transpose step.

Occasional Contributor
Posts: 19

Re: Changing variable data from . to Zeros

I used proc transpose because I need one id with each item code strung out horizontally rather than stacked vertically.  I am not sure on how to use the array with a loop but I will give it a try.

Ask a Question
Discussion stats
  • 16 replies
  • 155 views
  • 1 like
  • 6 in conversation