topic Re: Group By in SAS with a Count returns ID locations in table instead of actually IDs in SAS Procedures

Group By in SAS with a Count returns ID locations in table instead of actually IDs

solelo — Mon, 01 Aug 2022 16:34:46 GMT

When I run a PROC SQL to create a table and in the table I'm retying the user IDs but counting the amount of jobs they have done it returns a C00xx numbers instead of the actual user ID, below is the code I'm using, it's pretty straight forward but it doesn't return the user ID at all with the count. If I remove the count it returns the user ID

PROC SQL ; 
    CREATE TABLE TEST_2 AS 
       SELECT USER_ID , 
       COUNT(JOBS_COMP) AS JOBS_LOW

   FROM MAIN_INFO WHERE JOBS_COMP <= 30 
     GROUP BY USER_ID ;

QUIT;

MAIN_INFO

USER_ID	JOB_DATE	JOBS_COMP
AB123	020522	1
BA123	040722	1
AB123	030922	1
BA123	061522	1
AC123	010622	1
CA123	051322	1
AC123	071822	1

Current Output

USER_ID	JOBs_LOW
C0001	2
C0002	2
C0003	2
C0004	2

Desired Output

USER_ID	JOBS_LOW
AB123	2
BA123	2
AC123	2
CA123	2

Re: Group By in SAS with a Count returns ID locations in table instead of actually IDs

PaigeMiller — Mon, 01 Aug 2022 16:30:58 GMT

Show us a portion of your data set MAIN_INFO as working SAS data step code (please test it before posting to make sure the code is working) which you can type in yourself or by following these instructions (and not in any other format). Show us a portion of the output that is not correct.

Re: Group By in SAS with a Count returns ID locations in table instead of actually IDs

ballardw — Mon, 01 Aug 2022 16:27:36 GMT

From the code shown and the description of " a C00xx numbers instead of the actual user id" it appears that one of these may be happening: The values of the User_id in that set are not as you expect or the variable that has the value you expect is not actually "User_id".

You might also show the LOG from running your code. Include the code and any notes. Copy the text from the LOG and paste into a text box opened on the forum with the </> icon that appears above the message window.

@solelo wrote:

When I run a PROC SQL to create a table and in the table I'm retying the user IDs but counting the amount of jobs they have done it returns a C00xx numbers instead of the actual user ID, below is the code I'm using, it's pretty straight forward but it doesn't return the user ID at all with the count. If I remove the count it returns the user ID
PROC SQL ; 
    CREATE TABLE TEST_2 AS 
       SELECT USER_ID , 
       COUNT(JOBS_COMP) AS JOBS_LOW

   FROM MAIN_INFO WHERE JOBS_COMP <= 30 
     GROUP BY USER_ID ;

QUIT;

Re: Group By in SAS with a Count returns ID locations in table instead of actually IDs

PaigeMiller — Mon, 01 Aug 2022 17:01:37 GMT

There's nothing in your code that will turn the data you show into the output that you show.

Therefore, the problem is that the data in data set MAIN_INFO is not what you say it is; or the output data set TEST_2 is from some other code; or the code is not what you show us.

Please show us the entire LOG for this PROC SQL, copy the log as text and paste it into the window that appears when you click on the </> icon.
Please show us a screen capture (not typed) of the output data set TEST_2 so we can see portions of the data and the data set name.
Please show us a screen capture of the data set MAIN_INFO so we can see portions of the data and the data set name.

Example: here is a screen capture, you can see the data set name is SASHELP.CLASS. Use the "Insert Photos" icon to include your screen captures of the output and data; do not attach files.

Re: Group By in SAS with a Count returns ID locations in table instead of actually IDs

Tom — Mon, 01 Aug 2022 17:39:02 GMT

What did you use to produce the first report you showed? Did you run something like:

select * from main_table;

If so then the USER_ID you see in the column header of the report might be the LABEL attached to the variable and not the NAME of the variable. Check the output of PROC CONTENTS for your MAIN_TABLE.

proc contents data=main_table;
run;

Re: Group By in SAS with a Count returns ID locations in table instead of actually IDs

Sajid01 — Sun, 21 Aug 2022 13:03:26 GMT

The output is what can be expected from the code and the main info that has been provided in the code.
The main info does not have C0's. There will be none in the output.
To get them additional code will be needed to do the transformation. This depend on your business rules.