topic Re: Changing variable data from . to Zeros in SAS Programming

Changing variable data from . to Zeros

overky — Wed, 12 Apr 2017 15:46:49 GMT

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;

Re: Changing variable data from . to Zeros

collinelliot — Wed, 12 Apr 2017 16:07:11 GMT

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.

Re: Changing variable data from . to Zeros

RW9 — Wed, 12 Apr 2017 16:24:49 GMT

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 .

Re: Changing variable data from . to Zeros

overky — Wed, 12 Apr 2017 16:32:24 GMT

Do you have sample code?

Re: Changing variable data from . to Zeros

overky — Wed, 12 Apr 2017 16:34:40 GMT

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

Re: Changing variable data from . to Zeros

collinelliot — Wed, 12 Apr 2017 16:38:21 GMT

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;

Re: Changing variable data from . to Zeros

overky — Wed, 12 Apr 2017 16:44:56 GMT

\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.

Re: Changing variable data from . to Zeros

RW9 — Wed, 12 Apr 2017 17:24:22 GMT

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;

Re: Changing variable data from . to Zeros

overky — Wed, 12 Apr 2017 17:57:13 GMT

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.

Re: Changing variable data from . to Zeros

overky — Wed, 12 Apr 2017 18:02:02 GMT

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;

Re: Changing variable data from . to Zeros

Reeza — Wed, 12 Apr 2017 19:34:49 GMT

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;

Re: Changing variable data from . to Zeros

collinelliot — Wed, 12 Apr 2017 19:38:54 GMT

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?

Re: Changing variable data from . to Zeros

overky — Wed, 12 Apr 2017 20:01:42 GMT

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:

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;

Re: Changing variable data from . to Zeros

ballardw — Wed, 12 Apr 2017 21:02:35 GMT

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.

Re: Changing variable data from . to Zeros

overky — Wed, 12 Apr 2017 22:12:13 GMT

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.

Re: Changing variable data from . to Zeros

Patrick — Wed, 12 Apr 2017 22:22:00 GMT

@overky

If this is only about printing and not about changing the actual values of missing then use:

options missing='0';

Re: Changing variable data from . to Zeros

overky — Wed, 12 Apr 2017 22:25:45 GMT

no I need to change the values because I am exporting the dataset to a .dbf