Fill missing values with the previous values

[Demographer]

I copied/pasted in Excel the output of a proc tabulate and then imported it in SAS. When I tried the out statement, it gaves me only one column for variables X1 X2. I suppose this was probably not the most efficient way.

[RW9]

No, its really not.  Use out= from tabulate, then manipulate the output dataset to get what you want.  Alternative, use one of the other procedures (means/summary/SQL aggregates) or one of the other hundreds of ways to get output you want.  For example, your output doesn't make any sense to me:

SEX	COUNTRY	PERIOD	X1	X2
MALE	A	2000	2	3
			2	2
			2	2

 Why are there three rows, there is no identifer on each row to say what the rows reflect other then the first one?

[Demographer]

It is the output of a proc tabulate (cels below were merged).

[RW9]

Yes, but it doesn't make much sense.  

You have a male, from country A in period 2000, who has reulsts 2,3 (no information as to what the 2/3 represents).  You then have two more rows 2/2 2/2.  Which is it 2/3, 2/2 or 2/2?  Or was it three timepoints within period 2000?  Post some test data if you want some code on how to do it directly.

[Demographer]

Don't pay attention to the data. I cannot copy the real values for confidentiality purposes. These are just random values. There is also another row that I forget to include which was the age group.

[data_null__]

---

@Demographer wrote:

I copied/pasted in Excel the output of a proc tabulate and then imported it in SAS. When I tried the out statement, it gaves me only one column for variables X1 X2. I suppose this was probably not the most efficient way.

---

PROC TABULATE can create a data set directly using OUT= on the PROC statement.   This would avoid all the headaches associates with a trip to EXhell and back.

You can more easily fix your data imported from EXhell using the update trick

data haveV/view=haveV;
   set have;
   retain dummy 1;
   run;
data want;
   update haveV(obs=0 keep=dummy) haveV;
   by dummy;
   output;
   drop dummy;
   run;

[Demographer]

I tried this. The problem is that I want 2 separate column for X1 and X2 (these are 2 categories of a same variable). When I used the out= statement, they appear in a single column.

[RW9]

Ok then, at a guess:

proc means data=have;
  by sex country period age_group;
  var thevar;
  where thevar="A";
  output out=tab1 n=x1;
run;
proc means data=have;
  by sex country period age_group;
  var thevar;
  where thevar="B";
  output out=tab2 n=x2;
run;
data want;
  merge tab1 tab2;
  by sex country period age_group;
run;

 You could also proc means to get your overall output then transpose the data up, or count it directly in a datastep etc.

[today]

I have the newest version of sas 9.4.  I used to use this code but now it only works on the first retain statement.  All subsequent retain statements give the error message " 

retain _analyst;
------
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

"

any ideas why this doesn't work now?

I tried retain _ALL_ and this doesn't help.

[Tom]

---

@today wrote:

I have the newest version of sas 9.4.  I used to use this code but now it only works on the first retain statement.  All subsequent retain statements give the error message " 

retain _analyst;
------
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

"

any ideas why this doesn't work now?

I tried retain _ALL_ and this doesn't help.

---

Please open a new thread for your question.  Include more lines from the log so that the error message is shown in context.  Also use the Insert Code button so that formatting (like what word is underlined) is preserved.