topic Re: Extract value from a column based on other column to make a new column using proc sql/query buil in New SAS User

Extract value from a column based on other column to make a new column using proc sql/query builder

req41273 — Tue, 28 May 2019 14:50:24 GMT

I'm using proc sql code to create a table that displays output data as below:

phnumber Attrrecno

444333888 2 389

4328883333 391

8388888833 389

8388283888 391

82383838383 389

99293939393 391

My issue is, I need to create separate columns for the data under the phnumber column. The new columns will contain either a mobile phone number or home phone number based on the value under attrrecno. If attreccno is 389, it's home number. If 391 it is mobile. Using proc sql how do I do this. Or, can it be done on the output side using SAS EG Query Builder? I'm sending attachment as well to show my current proc sql code.

Re: Extract value from a column based on other column to make a new column using proc sql/query buil

PaigeMiller — Tue, 28 May 2019 14:55:02 GMT

proc sql;
    create table want as select case when attrrecno=389 then 
              phnumber else . end as home_number,
          select case when attrrecno=391 then phnumber else . end 
                  as mobile_number,
          attrrecno 
     from have;
quit;

Re: Extract value from a column based on other column to make a new column using proc sql/query buil

req41273 — Tue, 28 May 2019 16:10:16 GMT

Will try your suggestion.

Also, received following suggestion (see attachment doc). Getting a syntax error regarding the select subqueries. SAS error says expecting a , ), <>. and, or, between, etc.

Do you see anything wrong with the code syntax in the attachment that would cause this error?

thanks

Re: Extract value from a column based on other column to make a new column using proc sql/query buil

Tom — Tue, 28 May 2019 17:12:39 GMT

Post a copy of the SAS log with the error message. Do NOT attach a file. Instead use the Insert Code button in the forum editor (looks like {i}) to get a pop-up window that you can use to paste the text lines from the log.

Re: Extract value from a column based on other column to make a new column using proc sql/query buil

ballardw — Tue, 28 May 2019 18:03:01 GMT

@Tom wrote:

Post a copy of the SAS log with the error message. Do NOT attach a file. Instead use the Insert Code button in the forum editor (looks like {i}) to get a pop-up window that you can use to paste the text lines from the log.

And it is not even a log entry.