Drop Source column in Transposed table and re-arrange the rows

[ralizadeh]

Is there a setting in SAS EG that will allow me to remove the Source column and re-arrange the Age Category column in the following order: Age18, Age 18-64, then Age 65+ (the ascending and descending option is ineffective)? Please refrain from recommending any SAS Code unless you are sure it is the only way I can achieve these two objectives. I am running on an SAS server and accessing the MIS/DSS database. If SAS EG can handle it on its own, I don't want to deal with the complexity of changing my base code. It is already complicated enough! 😀

Thanks in advance.

Age Group in ascending order

Age Group in descending order:

[PaigeMiller]

You don't say how you are creating this table. Your column AGE CATEGORY is a character variable which will sort alphabetically (with the '<' symbol coming last if you sort ascending). Your mistake is creating text representations of categories of numeric variables. This is not a good practice. Had you left this age variable as numeric, it will sort numerically, which seems to be what you want.

Assuming you have a numeric variable called AGE, then you need to leave it numeric and create a custom format for AGE. Once you apply this format, and then sort, the numbers will sort in the table numerically.

proc format;
    value agef 0-17 = '<18' 18-64='18-64' 65-high='65 +';
run;

So now you apply this format to your age variable, in the PROC that created this table. The PROC will use the format to do the grouping while leaving the variable age as numeric, and so this will sort numerically, 0-17 first, 18-64 next and 65+ last.

format age agef.;

You may have to specify ORDER=INTERNAL, depending on the PROC used.

Since you are using SAS EG, it is possible to obtain the SAS code used by EG to create this table, and then make the changes above. You can also remove the SOURCE column in the PROC used to create this table.

[ralizadeh]

Hi @PaigeMiller 

The table is made using the transpose task, as I stated in the question's title. Additionally, I preferred to use EG options rather than create new codes. But I followed your advice because, initially, it seemed to work with some EG settings. Later, I discovered that it would incorrectly group by this newly added column.

Let me know if you have any idea as to why. Did I miss something there? Thanks for help.

Created new formatted column query

Output

Then group by month and formatted age

Output: It is not grouping. 

[PaigeMiller]

Transposed table (that's what the title says) doesn't necessarily have to come from the Transpose task. The table you showed in your first post has really 9 data points — 3 ages times 3 months. The data you show now has many many many data points. So not the same data, and something else has happened to go from many many many data points to 9 data points. This needs to be explained.

And it seems to me that the transpose task simply isn't appropriate here; perhaps you need PROC REPORT and not PROC TRANSPOSE, but I'm guessing because I need an explanation (as I said above); and I don't really know what EG calls their interface to PROC REPORT

[ralizadeh]

@PaigeMiller Unfortunately, none of your proposed solutions/comments are helpful for my situation right now, but I do appreciate you contribution.

[ballardw]

Print that file with the format replaced by something like Best. for the age formatted variable.

I bet you see two different values under 18 for month 10.

Unfortunately we 1) do not have your data 2) do not have the history of everything done with that data set by which methods.

I don't think the "query" to build a "formatted column" means that SQL will group on that formatted value (unless possibly you go into significantly more pointing and clicking) and SQL is the basic "query" related tool. Your result appears to be that of a simple Sql group by and count. Export the code generated from the project and then share that.

Bet we see something like:

proc sql;
    creat table <somename) as
    select month, 'age formatted'n, count(*) as Cin
    from yourdataset
   group by month, 'age formatted'n
   ;
quit;

I think you may be misunderstanding what that "summary groups" is doing. Bet it uses the values of the variables selected as I don't see anything in that picture that says "group by the formatted value", it just shows the variables selected.  If I saw something like:  put(age,ageformatname.) as Age_groupVariable then I would expect that new Age_groupvariable would group as expected because there would only be the one value in the result, that of the format applied.

The "formatted" value in the query you show would be the same as any other numeric format such as the Dollarw.d format. It assigns a format but the format doesn't change the underlying values.

[ralizadeh · Accepted solution]

Although I was unable to rearrange my age category column, I was able to remove the 'source' column. It was as easy as making a new Query. I included all of the columns needed for 'Select Data,' but not the 'source' column. That's all. There might be a better approach, but I haven't discovered one yet.