topic Re: Need to separate phone numbers into two separate columns in New SAS User

Need to separate phone numbers into two separate columns

req41273 — Thu, 30 May 2019 00:13:57 GMT

proc sql noprint /*INOBS=100*/;

		create table MDM_Members1 as
          SELECT DISTINCT MI.IDNUMBER AS SUBSCRIBER_ID,
          MP.PHNUMBER AS NEHII_PHNUMBER,
         (select MP.PHNUMBER FROM MDM.MPI_MEMPHONEWC MP WHERE MP.ATTRRECNO = MA.MEMRECNO = MI.MEMRECNO AND MP.ATTRRECNO = 389) AS HOME_NUMBER,
         (select MP.PHNUMBER FROM MDM.MPI_MEMPHONEWC MP WHERE MP.ATTRRECNO = MA.MEMRECNO = MI.MEMRECNO AND MP.ATTRRECNO = 391) AS MOBILE_NUMBER,
         MA.STLINE1 AS NEHII_STREET_ADDRESS1,
		 MP.ATTRRECNO AS ATTRECNO,
         MA.CITY AS NEHII_CITY,
         MA.STATE AS NEHII_STATE,
         MA.COUNTY AS NEHII_COUNTY,
         MA.ZIPCODE AS NEHII_ZIPCODE
         FROM MDM.MPI_MEMPHONEWC MP, MDM.MPI_MEMADDRWC MA, MDM.MPI_MEMIDENTWC MI
         WHERE /* MP.RECSTAT ='A' AND MI.RECSTAT='A' AND */
         MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO
         AND MP.ATTRRECNO IN (389,391)
         AND MP.MEMRECNO = MA.MEMRECNO
         AND MP.MEMRECNO = MA.MEMRECNO AND MA.ATTRRECNO = 390;
		
		 	QUIT;
			RUN;

Using mp.attrrecno values how do I modify my code to get two separate columns for the mp.phnumber column? See how my output looks like now in the attachment.

Re: Need to separate phone numbers into two separate columns

req41273 — Thu, 30 May 2019 00:17:02 GMT

I mean see doc8 attached for my output

Re: Need to separate phone numbers into two separate columns

ballardw — Thu, 30 May 2019 15:10:09 GMT

@req41273 wrote:

proc sql noprint /*INOBS=100*/;

		create table MDM_Members1 as
          SELECT DISTINCT MI.IDNUMBER AS SUBSCRIBER_ID,
          MP.PHNUMBER AS NEHII_PHNUMBER,
         (select MP.PHNUMBER FROM MDM.MPI_MEMPHONEWC MP WHERE MP.ATTRRECNO = MA.MEMRECNO = MI.MEMRECNO AND MP.ATTRRECNO = 389) AS HOME_NUMBER,
         (select MP.PHNUMBER FROM MDM.MPI_MEMPHONEWC MP WHERE MP.ATTRRECNO = MA.MEMRECNO = MI.MEMRECNO AND MP.ATTRRECNO = 391) AS MOBILE_NUMBER,
         MA.STLINE1 AS NEHII_STREET_ADDRESS1,
		 MP.ATTRRECNO AS ATTRECNO,
         MA.CITY AS NEHII_CITY,
         MA.STATE AS NEHII_STATE,
         MA.COUNTY AS NEHII_COUNTY,
         MA.ZIPCODE AS NEHII_ZIPCODE
         FROM MDM.MPI_MEMPHONEWC MP, MDM.MPI_MEMADDRWC MA, MDM.MPI_MEMIDENTWC MI
         WHERE /* MP.RECSTAT ='A' AND MI.RECSTAT='A' AND */
         MA.MEMRECNO = MI.MEMRECNO = MP.MEMRECNO
         AND MP.ATTRRECNO IN (389,391)
         AND MP.MEMRECNO = MA.MEMRECNO
         AND MP.MEMRECNO = MA.MEMRECNO AND MA.ATTRRECNO = 390;
		
		 	QUIT;
			RUN;

Using mp.attrrecno values how do I modify my code to get two separate columns for the mp.phnumber column? See how my output looks like now in the attachment.

What are the actual rules for creating the "two separate columns", is your current mp.phnumber a character value or numeric, and example starting values?

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

When I see variable names like NEHII_CITY I tend to suspect that you may be attempting to create a different set of variables for each area. Often that is much less efficient in general processing than just providing one additional variable that would hold the value of "Nehii" and the other values instead of creating potentially thousands of variables.

Re: Need to separate phone numbers into two separate columns

req41273 — Thu, 30 May 2019 16:51:42 GMT

Rules for phnumber field is if 389 it is home phone. if 391 it is a mobile phone number. phnumber field is numeric.

Re: Need to separate phone numbers into two separate columns

ballardw — Thu, 30 May 2019 19:53:22 GMT

How about providing some example start data with just 3 fields; an ID variable (to identify any related records) your ATTRRECNO variable and the PHNUMBER. Provide data for at least 4 different IDs and some with both types of ATTRECNO and some with only one. AS data step code.

Then using that example data show us what the result should look like.