Create new variable names from a list of values from another variable

[SanKH1]

If you have a table generated in SAS by proc freq such as:

age	frequency
1	1
4	2
6	1
11	1
22	3

How do you create a list of variables based on all the values of age, new variables: age_1, age_4, age_6, age_11, age_12?

[PaigeMiller]

Best to leave the data in long format, where SAS can treat it like data. As soon as you put the age number into a variable name, you are making your programming more difficult. What do you anticipate is the next step with this data, if you did transform it?

[SanKH1]

Thank you for your answer. This is a sample of the dataset we are initially working with:

ID	age
1	131
1	0
1	0
2	22
2	76
3	0
3	48
3	48
3	3

We then need to ran 

proc freq data=dsin;

table ID*age;

run;

And the output looks like:

	age
ID	0	3	22	48	76	131
1	2	0	0	0	0	1
2	0	0	1	0	1	0
3	1	1	0	2	0	0

Ultimately we want a table that counts IDS with at least one form by age. IDs that have more than one count for the same age, can only be accounted once. Like ID1 that has two counts for age =0 but it only adds one to the total of IDs.

Age	Count
0	2
3	1
22	1
48	1
76	1
131	1

[PaigeMiller]

If you want a table or report, instead of a SAS data set, you don't need to convert the SAS data set to horizontal. You can use PROC REPORT without converting the data set to horizontal.

But it's really not clear to me the final table is going to look like. It seems as if you want the final table to be vertical, which isn't what was implied earlier. And it seems as if you are treating age as a discrete set of values, they can only be 0, 3, 22, 48, 131 (?), is that really a good assumption?

[SanKH1]

It will be great to use proc report. We don't need to create sas dataset. Although the output might be used to create figures.

The final output table was depicted in a vertical way but there is no preference, as long as the results are the same. Age values vary across datasets. Some datasets have age values 0,3,22,48, etc. some other might have 1,22,56,131, etc.

[ErikLund_Jensen · Accepted solution]

Hi @SanKH1 

Your ultimate result table can be obtained directly from your input table with a small SQL select. The detour over Proc Freq is quite unnessary.

data have;
  input ID age;
  cards;
1	131
1	0
1	0
2	22
2	76
3	0
3	48
3	48
3	3
;
run;

proc sql;
  create table want as
    select age, count(age) as Count
    from (select distinct ID, age from have)
    group by age;
quit;

Result:

The subselect removes duplicates, and the main select counts occurrences per age.

[SanKH1]

Oh wow! So simple :O. Thank you so much for your help and explanation!

[SanKH1]

If I might ask, going back to another question I had posted before, I've realized that by converting my data into wide format I complicate things. Let's say you have the same data structure in the long format. But now we also need to get the cumulative number of people who had an incident up until every age. The tricky part is that once a person has an incident and it is accounted for, if this person has incidents after that, it doesn't contribute to increasing the count anymore. Let's say for ID3 it presents an incident at age 1, 3 and 48. But this person can only contributes to the count at age 1. And also, if they have more than one incident at the same age, it counts as 1. The output should look like this:

Age	Cumulative
0	2
3	2
22	3
48	3
76	3
131	3

[ErikLund_Jensen]

Hi @SanKH1 

Sorry for the late answer, but I live in europe and went to bed before I received your next question.

We can twist the sql select to do that.

data have;
  input ID age;
  cards;
1	131
1	0
1	0
2	22
2	76
3	0
3	48
3	48
3	3
;
run;

proc sql;
  create table want as
    select age, count(age) as Cumulative
    from (select distinct ID, min(age) as age from have group by id)
    group by age;
quit;

What happens here is that the subselect creates a distinct list with ID and minimum age,

and the outer select counts occurences of minumum age.

Result:

[ErikLund_Jensen]

Hi @SanKH1 

Sorry, I misunderstood the "cumulative" part. I have just read your other post, and the answer from @andreas_lds seems to solve your problem.

[SanKH1]

Thank you looking at my question after! I really appreciate it.