BookmarkSubscribeRSS Feed
overky
Calcite | Level 5

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;

 

16 REPLIES 16
collinelliot
Barite | Level 11

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.

overky
Calcite | Level 5

Do you have sample code?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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 .

overky
Calcite | Level 5

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

collinelliot
Barite | Level 11

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;

overky
Calcite | Level 5

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

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
overky
Calcite | Level 5

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.

 

 

overky
Calcite | Level 5

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;

Reeza
Super User

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;

 

 

collinelliot
Barite | Level 11

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?

 

overky
Calcite | Level 5

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;

ballardw
Super User

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.

overky
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 1215 views
  • 1 like
  • 6 in conversation