topic Re: Separating data from a table into 2 separate tables in New SAS User

Separating data from a table into 2 separate tables

wheddingsjr — Fri, 18 Dec 2020 04:12:43 GMT

Hi all

I have a table that has a column that contains numeric variables. I want to create 2 separate tables based on the length of the number. If the number has more than 6 digits it should go in 1 table and all the rest into another. Is that possible? Any assistance would be appreciated.

Re: Separating data from a table into 2 separate tables

wheddingsjr — Fri, 18 Dec 2020 04:24:34 GMT

I should have probably posted this as well. This is the query that is used to create the table. The MRN column is the column that I want to separate.
``PROC SQL;
CREATE TABLE ed.mrn_cohort_a AS
SELECT DISTINCT a.*
, C.MRN AS mrn
FROM work.datapull_1 a
LEFT JOIN EPICFIN.COVERAGE b
ON a.member_id= b.SUBSCRIBERID
LEFT JOIN EPICPAT.PATIENT C
ON B.SubscriberPatientID=C.PATIENTID
AND a.member_id ne ''
;QUIT;``

Re: Separating data from a table into 2 separate tables

andreas_lds — Fri, 18 Dec 2020 05:51:56 GMT

If you want to check the number of digits, you have to convert the number into a string, then you could use the length-function. But since a number with more than six digits is larger than 999999 you should just check the value. Separating data is only necessary in some very rare cases, so you may want to explain the steps to follow-up so that maybe better solutions can be found.

If you have to separate the data, try the following data-step:

data work.SmallNumbers work.LargerNumbers;
  set have;
  if MRN <= 999999 then output work.SmallNumbers;
  else output work.LargerNumbers;
run;

Re: Separating data from a table into 2 separate tables

Astounding — Fri, 18 Dec 2020 06:14:23 GMT

How many digits in 1.234567?
How about -12345678?