Repeat observations and create a dummy variable based on the value of an variable

[tomtom09]

Hi everyone, I am very new to SAS and have a question that I have struggled for a while. I have dataset from a survey of people's visits to parks. What I am trying to do is transforming the first data format into the second data format. The basic idea is that if a person visits parka twice, parkc once, then the visits will contribute to the dataset by 3 (1+2) times (4 choiceoccasions). Here I want to treat each visit as a choice he is making so that I would code varialbe choice for the park he visited as 1 otherwise 0. Similarly, the second person id2's visits will be augmented by (1+2+1) four times and the choice variable is coded similarly as for id 1. For each person there will be a fixed number of 5 choiceoccasions and if their total visits is less than 5, then the remaining choice variable will be 1 for no visit, 0 for the parks.

Can anyone help me out? Really really appreciate it! 

id	parkname	#visits
1	parka	2
1	parkb	0
1	parkc	1
2	parka	1
2	parkb	2
2	parkc	1

Desired format:

id	park	choice	choiceoccasion
1	parka	1	1
1	parkb	0	1
1	parkc	0	1
1	no visit	0	1
1	parka	1	2
1	parkb	0	2
1	parkc	0	2
1	no visit	0	2
1	parka	0	3
1	parkb	0	3
1	parkc	1	3
1	no visit	0	3
1	parka	0	5
1	parkb	0	5
1	parkc	0	5
1	no visit	1	5
1	parka	0	5
1	parkb	0	5
1	parkc	0	5
1	no visit	1	5
2	parka	1	1
2	parkb	0	1
2	parkc	0	1
2	no visit	0	1
2	parka	0	2
2	parkb	1	2
2	parkc	0	2
2	no visit	0	2
2	parka	0	3
2	parkb	1	3
2	parkc	0	3
2	no visit	0	3
2	parka	0	4
2	parkb	0	4
2	parkc	1	4
2	no visit	0	4
2	parka	0	5
2	parkb	0	5
2	parkc	0	5
2	no visit	1	5

[LinusH]

Looks like an awkward transformation.

Can you tell us why you want the desired format? What type of report are you intend to create in the end?

[tomtom09]

Hi LinusH, that's because I am estimating a repeated conditional logit model. I am treating each of the visits as a choice that person makes in that occasion. If there are in total of 3 parks and he visited one of these, then we will assume he chose that park out of 3. Conditional logit model is commonly used in applied economics research studying consumer preferences and economic value of public goods and its response has to be 0 or 1. Actually I am studying people's valuation for park facilities, which will be my independent variables. Let me know if you need further clarification. Thanks so much!

[tomtom09]

Anyone knows how to do it? Any help will be very much appreciated!

[LinusH]

By quickly looking at the scenario, I would probably solve this by data step programming, using a combination of RETAIN, BY, DO-loops and conditional assignments and explicit OUTPUT.

But it requires some systematization, and trial and error programming.

If you don't have the capacity to do this on your own, find a colleague that can help you, or if you have the time, get some training.