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 |
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.
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;
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.
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.
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;
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.
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.