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?
Hi @ANKH1
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.
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?
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 |
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?
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.
Hi @ANKH1
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.
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 |
Hi @ANKH1
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:
Hi @ANKH1
Sorry, I misunderstood the "cumulative" part. I have just read your other post, and the answer from @andreas_lds seems to solve your problem.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: