topic Sort is removing leading numbers in SAS Programming

Sort is removing leading numbers

Rsadiq — Sat, 04 Feb 2023 05:18:35 GMT

Hello

I am having an issue with my data steps removing leading 1's from my variable, for example test.prov2 is being sorted by key which I need the dataset sorted by (test.prov2) but in that case the id is losing the leading 1's. How do I not remove the 1's (from variable key)when sorting or merging? Example is attached i want to keep the 1;s in key but they are being dropped.

proc sort data=test.prov2 ;
by key;
format id $20.;
run;

proc sort data=test1.prov;
by key;
run;

data hce.provider_merge5;
merge test.prov2 test1.prov;
length id $20;
by key;
run;

Re: Sort is removing leading numbers

Kurt_Bremser — Sat, 04 Feb 2023 07:20:01 GMT

PROC SORT will not change any data in variables, it will only reorder observations and, if NODUPKEY is used, remove complete observations. There will not be any change to the content of variables.
Are you sure that id is only contained in dataset prov2?

Re: Sort is removing leading numbers

Patrick — Sat, 04 Feb 2023 08:16:03 GMT

As already mentioned Proc Sort doesn't change values.

I don't understand how the attached document shows the issue you're describing.

Just having a theory: If you move the length statement before the merge statement does the issue still persist? ....and do you get truncation warnings in the SAS log?

data hce.provider_merge5;
length id $20;
merge test.prov2 test1.prov;
by key;
run;

Re: Sort is removing leading numbers

Rsadiq — Sat, 04 Feb 2023 14:43:14 GMT

For example the document shows that it is 111111n for example but after sorting the result shows n for id. It truncates the 1's for some reason. I just wanted to see how to fix that since I need the full ID

Re: Sort is removing leading numbers

Rsadiq — Sat, 04 Feb 2023 15:21:06 GMT

For example after the sort

the below data step , i see the attached instead of the 1111s in front of the id, it shows just characters and letters. but i need the ones, why would the ones disapper in the datastep?

proc sort data=prov2 ;
by key;
format id $20.;
run;

Re: Sort is removing leading numbers

Rsadiq — Sat, 04 Feb 2023 15:24:39 GMT

For example after the sort

the below data step , i see the attached instead of the 1111s in front of the id, it shows just characters and letters. but i need the ones, why would the ones disappear in the datastep?

proc sort data=prov2 ;
by key;
format id $20.;
run;

Re: Sort is removing leading numbers

Kurt_Bremser — Sat, 04 Feb 2023 15:36:46 GMT

Remove the format statement. Run a PROC PRINT before and after the sort, then post the results here.

Do not attach Office documents; you can post the screenshot directly, using the camera icon.

Re: Sort is removing leading numbers

Rsadiq — Sat, 04 Feb 2023 15:42:58 GMT

showing before here (before the sort on key) the ids have 111's

after the sort this happens, the 1's vanish and only whatever is left after the 1's appears

Re: Sort is removing leading numbers

Kurt_Bremser — Sat, 04 Feb 2023 15:54:26 GMT

Run this (and ONLY this!):

proc print data=test.prov2 (obs=10);
run;

proc sort
  data=test.prov2 (obs=10)
  out=prov2_test
;
by key;
run;

proc print data=prov2_test;
run;

Post the complete log of the code by copy/pasting it into a window opened with this button:

Also post the result of both PRINTs in one screenshot.

Re: Sort is removing leading numbers

Tom — Sat, 04 Feb 2023 15:58:37 GMT

Sorting just changes the order. Since you are sorting by KEY and not by ID the location of the values of ID that start with 111 will be potentially be different than before. Did you search the whole file for them?

But the NODUPKEY option will remove OBSERVATIONS. So it is possible the observations with values of ID that start with 111 were deleted because there was already some other observation with that same value of KEY that was kept.

Re: Sort is removing leading numbers

Rsadiq — Sat, 04 Feb 2023 16:10:51 GMT

                                                         The SAS System                           09:09 Saturday, February 4, 2023

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=ACTIVEX;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HtmlBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS94/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         proc print data=test.prov2 (obs=10) ;
27         run;

NOTE: There were 10 observations read from the data set test.PROV2.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              1459.31k
      OS Memory           25776.00k
      Timestamp           02/04/2023 10:04:27 AM
      Step Count                        25  Switch Count  0
      Page Faults                       0
      Page Reclaims                     298
      Page Swaps                        0
      Voluntary Context Switches        5
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

28         
29         proc sort
30           data=test.prov2 (obs=10)
31           out=prov2_test
32         ;
33         by key;
34         run;

NOTE: There were 10 observations read from the data set test.PROV2.
NOTE: The data set WORK.PROV2_TEST has 10 observations and 2 variables.
NOTE: PROCEDURE SORT used (Total process time):
2                                                          The SAS System                           09:09 Saturday, February 4, 2023

      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              1293.62k
      OS Memory           25776.00k
      Timestamp           02/04/2023 10:04:27 AM
      Step Count                        26  Switch Count  2
      Page Faults                       0
      Page Reclaims                     143
      Page Swaps                        0
      Voluntary Context Switches        35
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

35         
36         proc print data=prov2_test ;
37         run;

NOTE: There were 10 observations read from the data set WORK.PROV2_TEST.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              340.03k
      OS Memory           25260.00k
      Timestamp           02/04/2023 10:04:27 AM
      Step Count                        27  Switch Count  0
      Page Faults                       0
      Page Reclaims                     32
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

38         
39         GOPTIONS NOACCESSIBLE;
40         %LET _CLIENTTASKLABEL=;
41         %LET _CLIENTPROCESSFLOWNAME=;
42         %LET _CLIENTPROJECTPATH=;
43         %LET _CLIENTPROJECTPATHHOST=;
44         %LET _CLIENTPROJECTNAME=;
45         %LET _SASPROGRAMFILE=;
46         %LET _SASPROGRAMFILEHOST=;
47         
48         ;*';*";*/;quit;run;
49         ODS _ALL_ CLOSE;
50         
51         
52         QUIT; RUN;
53

115Obs prov_id PROVIDER_KEY

2	11	55
3	111	555
4	1111	5555
5	11111	55555
6	111111	555555
7	111111d	5555551
8	111111a	5555554
9	1111111	5555555
10	1111113	5555556

results from both, i tried your code with a higher number of obs for example 200 and it truncates the 1's again but this logic is working for the obs=10

Re: Sort is removing leading numbers

Kurt_Bremser — Sat, 04 Feb 2023 16:18:08 GMT

My code proves that no truncation occurs, but the output clearly shows you have variable value lengths. Don't be surprised that the sort puts other (shorter) values to the top of the dataset when sorting more of the dataset.

Re: Sort is removing leading numbers

Patrick — Sat, 04 Feb 2023 16:35:31 GMT

If you merge two tables that have same named variables that are not part of the merge key then the value from the table listed last in the merge statement will "win".

I've renamed in below merge the id variables so they individual names per source table. Investigate the result and see if this explains to you where these "leading 1" went. It's certainly not a truncation issue and it certainly has nothing to do with a Proc Sort that never changes values.

proc sort data=test.prov2;
  by key;
run;

proc sort data=test1.prov;
  by key;
run;

data hce.provider_merge5;
  length id $20;
  merge test.prov2(rename=(id=id2)) test1.prov(rename=(id=id1));
  by key;
  if id1 ne id2 then output;
run;

Re: Sort is removing leading numbers

Rsadiq — Sat, 04 Feb 2023 17:37:17 GMT

yes but when i remove the obs statement from the steps , the id gets truncated in the end table. I did do a search to see if the 11's are somewhere else in the table in the ids but there is none. It does look like truncation is going on. I am just confused on why and how.

Re: Sort is removing leading numbers

Tom — Sat, 04 Feb 2023 17:38:07 GMT

I don't understand what you mean by "truncates the 1's". You clearly have values that have different numbers of the digit 1 in the front of them. Your two printouts are also showing different alignments of the values. The first table (the photograph you pasted in of your output) is showing the second column right aligned, like a numeric variable would be. But the second table (the html table you pasted in) is showing the second column left aligned like SAS would show a character variable.

Remember that ODS output "eats" the leading spaces. So if the first column has leading spaces then the HTML output like you seem to be showing will not show them. Regular LISTING output will show the leading spaces in PROC PRINT output. You can use the $QUOTE format with your character variable to have ODS output preserve the leading spaces since they will be enclosed in quotes. So you can see the difference between "111" and " 111" and why the second one would sort before the first.

But if you are actually merging two datasets by KEY and they both contain the variable ID then the value retained will be the one read last. In a normal one-to-one merge that means the last dataset listed "wins". But in a one-to-many or many-to-many merge it gets more complicated as datasets that have run out of observations for that value of the KEY variable are no longer being read in, so they do not overwrite the values read from the other dataset.

Re: Sort is removing leading numbers

Rsadiq — Sat, 04 Feb 2023 18:01:20 GMT

for example like i am sorting by key, result is as shown

but if im sorting by id, the 1's dont vanish why is that the case? Like 11111z is being transformed to just z. After doing a simple sort. This issue is not being solved. why is the id being truncated when i sort by key, when i sort by id it stays (the leading 1's)

Re: Sort is removing leading numbers

Tom — Sat, 04 Feb 2023 18:22:22 GMT

You seem be comparing apples to oranges. Your first photograph has one variable. Your second photograph has two variables. You cropped out the variable names when you took the picture of your screen so we cannot even tell if they are of the same variables. And we do not know the names of the dataset used to make either picture.

What is it that you are comparing?

Why not just look for ONE of the values that you thing is gone?

So if the variable in the first picture is named ID and the dataset is named HAVE then you can run this code to see the values of ID and KEY for the observations where ID has a value that would probably appear as 111 on your screen.

proc print data=have;
  var id key ;
  where strip(id)='111';
run;

Sort by a variable will definitely change the order that the values appear.

Example:

data have;
  input id :$10. key ;
cards;
1     9
11    8
111   7
1111  6
2     5
234   4
;

proc sort data=have; by id; run;
proc print; run;
proc sort data=have; by key; run;
proc print; run;

Re: Sort is removing leading numbers

Tom — Sat, 04 Feb 2023 19:34:34 GMT

Sorting is not your problem. If you have a problem it is probably caused by MERGING.

Your first posted this code :

proc sort data=test.prov2 ;
by key;
format id $20.;
run;

proc sort data=test1.prov;
by key;
run;

data hce.provider_merge5;
merge test.prov2 test1.prov;
length id $20;
by key;
run;

There are two mistakes in the code. First is the addition of a FORMAT statement in the first PROC SORT. That will not impact the sorting, but it might impact what the values look like when you display them. By attaching the $20. format specification to the ID variable in the TEST.PROV2 dataset you will not see anything in the variable past the first 20 bytes. So if ID have a length that is longer than 20 some values will be truncated WHEN DISPLAYED.

The second is the LENGTH statement in the data step. Where it is placed it does nothing. If ID was already on TEST.PROV2 (you should have a WARNING in the SAS Log on the first PROC SORT if it does not exist) then its length in HCE. PROVIDER_MERGE5 will be set by the length it has in TEST.PROV2. And if it is not in TEST.PROV2 then its length will be set by the length it has in TEST1.PROV. Only if it is not in either will the LENGTH statement do anything. And what it will do is create an empty character variable named ID.

If you are really seeing values of ID that exist in the TEST.PROV2 dataset and do not exist in the HCE.PROVIDER_MERGE5 dataset then that is because the value of ID was overwritten by the value of ID read from the TEST1.PROV dataset.

So please tell use information about your two input datasets. Do both datasets have KEY and ID variables? What TYPE of variables are they in each dataset? Are they NUMERIC or CHARACTER. And if they are character what is the LENGTH of the variable? Do any of the variables have any formats permanently attached to them in either dataset.

If ID exists on both datasets then the MERGE will have trouble.

First, as has been explained before, since the value of ID from TEST1.PROV will overwrite the values of ID from TEST.PROV2 for matching values of KEY.

Second it ID is defined LONGER in TEST1.PROV than in TEST.PROV2 then values read from TEST1.PROV could be truncated. But that would be right truncation, the ends of the values would be removed, not the beginnings.

So the most likely cause of values disappearing (or changing) is because you asked SAS to replace the values of ID in TEST.PROV2 with the value of ID from TEST1.PROV instead.

Re: Sort is removing leading numbers

Kurt_Bremser — Sat, 04 Feb 2023 19:21:07 GMT

Post your .sas7bdat file as an attachment. It is in the data, so we must see it.

Re: Sort is removing leading numbers

Sajid01 — Sun, 05 Feb 2023 18:51:40 GMT

Hello @Rsadiq
Please provide a sample of your data as sas7bdat file?