silasskovsbo Tracker

Adding rows by condition

silasskovsbo — Sun, 30 Mar 2014 16:03:29 GMT

I have a data set(call DATASET) as this (about 100000 observations):

X	Y	Z
a	j	0
b	k	1
c	l	0
d	m	0
e	o	1
f	p	0
g	q	0
h	r	0
i	s	1

I would like to do is to add one blank row above each row where Z=1.

to get this data set

X	Y	Z
a	j	0

b	k	1
c	l	0
d	m	0

e	o	1
f	p	0
g	q	0
h	r	0

i	s	1

Can anybody help and I would be very thankfull.

Best Silas, London

Missing table

silasskovsbo — Tue, 07 May 2013 09:49:19 GMT

Dear all.

I work with panel data (data sets with 5 mio. observations pr year and hundreds of variables) at University of Aarhus, department of Economics, and am looking for a program which can do the following:

For some variables are the all observations missing for some years.It would be of great use to have a program which could make a table with one observation for each year telling for all the variables whether this variable is fully missing for the specific year. Missing is defined as SAS standards for both numeric and characters.

Example of data

Header 1	Header 2	Header 3	Header 4
Personidentifier	Year	Male	Income
1	1999	1	1000
2	1999	0	1500
1	2000	1	.
2	2000	0	.
1	2001	1	1400
2	2001	0	.

And wanted table:

Header 1	Header 2	Header 3
Year	Male_miss	Income_miss
1999	0	0
2000	0	1
2001	0	0

Hope it makes sense and that somebody it able to help.

Thanks a lot!

Best // Silas

Re: Include and log

silasskovsbo — Mon, 25 Mar 2013 20:06:38 GMT

Thank you for the fast response.

Using /source2 helped a bit. Now I get the full log. But I do get the full log in one textile, logA.

This does not print two separate log-files for program A and one for program B.

Do you know how to get that? And can you make an exact example of it? Just with the mentioned data-statements above?

Thanks again!

Include and log

silasskovsbo — Mon, 25 Mar 2013 16:07:44 GMT

Hello all.. I have a problem.

I have several programs, 'program1', 'program2' etc, which shall be run in row. So first 'program1' and then 'program2' etc. I run my programs in batch mode and I have used the %include-statement to run program2 after program1.

My problem is that I want to save specific logs for each program.

How can I do that?

If the programs are for example:

Program 1:

data temp2;

set temp1;

run;

%include' program2'

Program 2:

data temp3;

set temp2;

run;

I have tried with proc printto and dm-statements but have no luck..

Hope you can help me

// Best, Silas Skovsbo, Aarhus University, Denmark

Re: proc format

silasskovsbo — Thu, 06 Dec 2012 15:54:42 GMT

Problem solved! Thank you very much all of you...

Very grateful! Best from Aarhus!

Re: proc format

silasskovsbo — Mon, 03 Dec 2012 13:25:40 GMT

Sorry, Anca 😕

There is still one small question.. This program works perfectly for all character variables, but in my data set there are also a lot of numeric variables.These should not be affected, but with the SQL-program they are made to missing.

So I want:

have

x(cha) y(cha) z(num)

1z400 this1 3

4 45 4

7 . 5

8isnt 0900 6

want

1z400 this1 3

numb numb 4

numb . 5

8isnt numb 6

But now the result is:

1z400 this1 .

numb numb .

numb . .

8isnt numb .

If I can get want, I can just format the numeric observations with a format statement.

Re: proc format

silasskovsbo — Mon, 03 Dec 2012 13:11:07 GMT

Perfect :smileygrin:

Now I get the perfect results...

Thank you all very much for the help!

Re: proc format

silasskovsbo — Mon, 03 Dec 2012 12:58:58 GMT

Then I do not understand what goes wrong.

I have attached my log when I try to run Arthur's program, can you see what goes wrong?

Re: proc format

silasskovsbo — Mon, 03 Dec 2012 12:00:46 GMT

Thank you. This returns a table showing the type of each observation.

But I need a format that does the below: (convert from have --> want)

have

1abc

bc2

...

1234

want

1abc

bc2

...

number

-----------------------------------

By this I will be able to find all the data in a data set with character variables which is not numbers.

Re: proc format

silasskovsbo — Mon, 03 Dec 2012 10:08:34 GMT

Thank you for all the great commentaries. Unfortunate it is still not really doing.

The program needs to automatically do it on all character variables, since there are a huge amount of variables.

I have tried your program, Arthur. But something seems to go wrong?? for the '&missing.' it says statement is not valid??

Re: proc format

silasskovsbo — Mon, 26 Nov 2012 15:38:10 GMT

Thank you for your suggestions.

As Tom mentions, it is a problem if a value as '1ABC' is in the interval '0'-'9999999999' since I want only pure numbers to be formatted, so I am able to find all the observation which differ and therefor is missing.

How can I then format all values which is a number, but not all other kinds of values, eg. '1ABC', '...', ' .' ?

Nondigit function?

Re: proc format

silasskovsbo — Mon, 26 Nov 2012 13:57:14 GMT

Thank you!

That solved the problem

so easy when you know the code!

Re: proc format

silasskovsbo — Mon, 26 Nov 2012 13:35:30 GMT

An example:

For two character variables, X and Y:

X Y

1 22

2 ....

3 . . .

. 32

.. 11

. . 78

Then I want a format which gives me:

X Y

numb numb

numb ....

numb . . .

. numb

.. numb

. . numb

proc format

silasskovsbo — Mon, 26 Nov 2012 12:59:41 GMT

Hey.

I have a data set with character variables. The values for the variable are numbers, ' ' (empty, as character missings should be), but there are also other kinds of missing values (eg. '...', '.. .' ' .')

I would like to make a format which for these different values take changes the values to : number --> 'numbers' and let all observations which are not numbers stay the same.

That will make it possible for me to find the different kind of missing values.

I hope you understand and have a good idea to how to create this format.

If you have questions, just ask

Best regards, Silas Skovsbo, Aarhus University, institute of economics.

Re: use of proc format

silasskovsbo — Mon, 08 Oct 2012 13:54:15 GMT

Thanks for help everybody... Problem seems to be solved now! :smileygrin:

Re: use of proc format

silasskovsbo — Mon, 08 Oct 2012 10:39:48 GMT

Hey Tom.

Your program does also work for me with the data set you make.

But unfortunate - and I do not understand why - it still does not work when I use the program on my original data set.

To show my problem more specific below I have uploaded my problemativ results:

This is the result when I do proc freq on original data

Then I use your format and I then have this result:

Which is clearly wrong.

Do you have any idea on how it fails. I need three categories 1: '.' 2: ' ' 3: 'other'

And it works perfect for me when I use your data set?

Thank again! Best / Silas

Re: use of proc format

silasskovsbo — Mon, 01 Oct 2012 15:12:25 GMT

Btw the reason I need to do is because when the danish statstics publics new data there is the problem that for character-variables it happens that missing data for some years are given "." and for other years are given " ". So at the institute of Economy in Aarhus we need to find out what years missing data is given the one or the other value.

Re: use of proc format

silasskovsbo — Mon, 01 Oct 2012 14:55:57 GMT

I have a dataset as shown in the my first question, just with a lot more observations and with a variable YEAR which shall not be affected by the format.

then I have tried this code inspired by you:

proc format;

value $cmiss

'b.'='.'

' '=' '

other='other'

;

value nmiss

.=.

other='other'

;

run;

proc freq data=XXX;

tables year*A / nocol nopercent missing;

format _numeric_ nmiss. _character_ $cmiss. year;

run;

but this just gives me that A only consist of " " and "other", the "." is included in "other".

Do you understand? I would like to know how to continue after the proc format step given that I have a data set with the name XXXX and the three variables YEAR, A, B?

Best //

Re: use of proc format

silasskovsbo — Mon, 01 Oct 2012 14:30:26 GMT

Hey Arthur.

Thanks for the fast answer, i see it works for with your code, but unfortunate it still not works with my original.

I have a data set with the variables YEAR and AKASSE. AKASSE does have codes " ", "." and other numbers.

When I only use the proc format of yours I only get " " and "other" in my proc freq results and "." are included in "other".

Do you understand?

So how can I use the proc format properly on the original data set?

Again thanks!

use of proc format

silasskovsbo — Mon, 01 Oct 2012 13:26:34 GMT

I have realised some strange problems with a command which should be quite simple, proc format.

The problem is as follows.

My data is like

A$ B

. 3

e .

I have as seen above both missing data for the character variable, A$, shich is given by "." and " "

I want to use proc format to change the format so the data looks like this

A$ B

. other

other

other .

It is because I then can make a proc freq which gives me

	.	other

Somebody who can help me?

Best regards, Silas, Aarhus University