BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
numediaweb
Calcite | Level 5

Being totally new to SAS, I don't know how to import this layout/dictionary file into SAS® Studio:

 

       *********************************************
       *                                           *
       *           A T T E N T I O N               *
       *                                           *
       *   THESE POS RECORD SPECIFICATIONS WERE    *
       *   PRODUCED FROM OUR DICTIONARY AT THE     *
       *   SAME TIME AS THE POS DATA FILE THAT     *
       *   YOU REQUESTED. YOU MAY WISH TO CHECK    *
       *   THESE SPECIFICATIONS TO SEE IF ANY      *
       *   CHANGES HAVE OCCURED SINCE YOUR RECEIPT *
       *   OF ANY PRIOR DOCUMENTATION.             *
       *                                           *
       *   FILE CREATION DATE = 01/02/2018         *
       *                                           *
       *********************************************
 DATE: 01/02/2018              POS RECORD LAYOUT                      PAGE: 1
                 Hospital, CATEGORY = "01" (SEE POSITIONS 3-4)

   SHORT DESCRIPTION                                LEN  START END    TYPE

   Provider Category Subtype Code                    2    1     2    VARCHAR2
     Description: Identifies the subtype of the provider, within the
                  primary category.  Used in reporting to show the
                  breakdown of provider categories, mainly for hospitals
                  and SNFs.
     SAS Name:    PRVDR_CTGRY_SBTYP_CD
     COBOL Name:  PRVDR-CTGRY-SBTYP-CD
     VALUES:      01=Short Term
                  02=Long Term
                  ....

I got this file from cms.gov and I want to convert/export the data inside this text file into a MySQL database but first how can I import this into SAS Studio (SAS University Edition)?

 

Sorry if I choose the wrong forum/location to post: I am a total newbie!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Reading this type of report into a dataset is usually just a matter of retaining some values and using conditional logic to process the various types of records.  Here is one method.

 

First make a copy of the file that gets rid of the empty lines and headers.

data _null_;
  infile tmpfile1 ;
  input ;
  firstcol=verify(_infile_,' ');
  if firstcol in (0,2,8,15) then delete ;
  if _infile_ =: '   SHORT DESCRIPTION' then delete;
  if _infile_ =: '     COBOL Name:' then delete;
  file tmpfile2 ;
  put _infile_;
run;

Now you can read the file into a list of variable metadata and list of code/decode metadata.

data variables(keep=varnum name xtype xlength start end label description)
     lookups(keep=varnum name code decode )
;
  length varnum 8 name $32 xtype $10 xlength 8 start 8 end 8 ;
  length label $100 description $500 ;
  retain varnum -- description ;
  length code decode $90 ;
  length firstcol 8 rectype $20 ;
  dlm=' ';
  infile tmpfile2 truncover dlm=dlm end=eof ;
  input @@ ;
  firstcol=verify(_infile_,' ');
  select (firstcol);
    when (4) do ;
      varnum + 1;
      input label $4-53 xlength start end xtype @@ ;
      if not eof then do until (firstcol ne 4) ;
        input / @@ ;
        firstcol=verify(_infile_,' ');
        if firstcol=4 then label=catx(' ',label,_infile_);
      end;
    end;
    when (6) do;
      rectype = left(scan(_infile_,1,':'));
      select (rectype);
        when ('SAS Name') do ;
          input @19 name ;
          output variables ;
        end;
        when ('Description') do ;
          input @19 description $90. @@;
          if not eof then do until (firstcol ne 19) ;
            input / @@ ;
            firstcol=verify(_infile_,' ');
            if firstcol=19 then description=catx(' ',description,_infile_);
          end;
        end;
        when ('VALUES') do until (firstcol ne 19) ;
          dlm='=';
          input @19 code decode $90. @@;
          output lookups;
          input / @@ ;
          firstcol=verify(_infile_,' ');
        end;
        otherwise do; put rectype= / _infile_ ; stop; end;
      end;
    end;
    otherwise do; put firstcol= / _infile_ ; stop; end;
  end;
run;

Example:

Obs varnum name                 xtype    xlength start end label

  1    1   PRVDR_CTGRY_SBTYP_CD VARCHAR2     2      1    2 Provider Category Subtype Code
  2    2   PRVDR_CTGRY_CD       VARCHAR2     2      3    4 Provider Category Code
  3    3   CHOW_CNT             NUMBER       2      5    6 CHOW Count
  4    4   CHOW_DT              DATE         8      7   14 CHOW Date
  5    5   CITY_NAME            VARCHAR2    28     15   42 Address: City

Obs description

  1 Identifies the subtype of the provider, within the primary category.  Used in reportin
  2 Identifies the type of provider participating in the Medicare/Medicaid program.
  3 Number of times this provider has undergone a change of ownership.
  4 Effective date of the most recent change of ownership for this provider.
  5 City in which the provider is physically located.

  

 Obs    varnum            name            code     decode

   1       1      PRVDR_CTGRY_SBTYP_CD    01       CLIA88 Laboratory
   2       2      PRVDR_CTGRY_CD          22       CLIA Laboratory
   3       7      CMPLNC_STUS_CD          A        IN COMPLIANCE
   4       7      CMPLNC_STUS_CD          B        NOT IN COMPLIANCE
   5      13      INTRMDRY_CARR_CD        00000    DUMMY FOR MEDICAID HHA
   6      13      INTRMDRY_CARR_CD        00010    BLUE CROSS (ALABAMA)
   7      13      INTRMDRY_CARR_CD        00011    CAHABA
   8      13      INTRMDRY_CARR_CD        00020    BLUE CROSS (ARKANSAS)
   9      13      INTRMDRY_CARR_CD        00040    BLUE CROSS (CALIFORNIA)
  10      13      INTRMDRY_CARR_CD        00060    BLUE CROSS (CONNECTICUT)

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

This looks like a collection of lookup tables or formats. Importing these with a data step will be well above "newbie" level. You should start your SAS journey with simpler tasks; once you have a solid grasp on the basics of reading text files, you can start with complex issues like this.

numediaweb
Calcite | Level 5

Thanks @Kurt_Bremser for the reply.
I am not actually trying to learn SAS but rather just do a one time conversion of the file above into something I can work with in PHP/MySQL world.

 

From the website of cms.gov they mention:

Compatible Software Programs: Microsoft Access, Microsoft SQL/DB2/Oracle, SAS or other statistical software. (Note: Microsoft Excel or TextPad cannot be used to analyze the POS file)

Kurt_Bremser
Super User

Well, they talk about compatibility, not about easiness.

 

Since these are lookups, I'd read that text into a dataset that can be used as control input for proc format.

One needs to identify lines that must be skipped, lines that contain meta-information about the formats (names, types), and lines that are data.

Then you can read the meta-information for fmtname and type, retain those, and convert the data lines to start and label variables, and write such lines out.

 

numediaweb
Calcite | Level 5

I see. So it is not an easy task 😞
I went and posted the same question on stackoverflow.com.. If there's no way to do the import in a SAS program I will do the txt parsing in php and extract the needed data.

 

Thanks for the help anyway.

Kurt_Bremser
Super User

If you are already familiar with an environment where you can solve it, use that. While SAS is not that hard to learn, it still takes time. And the format of your text is not one for which SAS has an out-of-the-box procedure. Isn't there something at the website (they might have some SAS code for download)?

numediaweb
Calcite | Level 5
I checked their site and couldn't find something related to that data I want to extract: there are some *.sas7bdat files but they don't cover the data set i am interested in 😞

I think I will just go ahead and do the parsing in PHP.. Thanks for your support 🙂
ballardw
Super User

@numediaweb wrote:

Thanks @Kurt_Bremser for the reply.
I am not actually trying to learn SAS but rather just do a one time conversion of the file above into something I can work with in PHP/MySQL world.

 

From the website of cms.gov they mention:

Compatible Software Programs: Microsoft Access, Microsoft SQL/DB2/Oracle, SAS or other statistical software. (Note: Microsoft Excel or TextPad cannot be used to analyze the POS file)


The information you show does not involve SAS at all. Large economy size clue: VARCHAR2. SAS has two variable types Numeric and Character and I have never seen a SAS supplied solution for a SAS data set use VARCHAR.

So you apparently should use that information as a description for you importing data into MYSQL not SAS if you want to use it in MySQL.

 

If you actually have a SAS data set then the information would be of minimal value as the SAS set is in a proprietary binary format and the column start./end information or variable type is not going to be much help and you would look for a direct import of SAS dataset into MySQL (or a connection from SAS to MySQL such as ODBC or SAS/Connect) which would provide the file information but not the variable limits.

Tom
Super User Tom
Super User

Reading this type of report into a dataset is usually just a matter of retaining some values and using conditional logic to process the various types of records.  Here is one method.

 

First make a copy of the file that gets rid of the empty lines and headers.

data _null_;
  infile tmpfile1 ;
  input ;
  firstcol=verify(_infile_,' ');
  if firstcol in (0,2,8,15) then delete ;
  if _infile_ =: '   SHORT DESCRIPTION' then delete;
  if _infile_ =: '     COBOL Name:' then delete;
  file tmpfile2 ;
  put _infile_;
run;

Now you can read the file into a list of variable metadata and list of code/decode metadata.

data variables(keep=varnum name xtype xlength start end label description)
     lookups(keep=varnum name code decode )
;
  length varnum 8 name $32 xtype $10 xlength 8 start 8 end 8 ;
  length label $100 description $500 ;
  retain varnum -- description ;
  length code decode $90 ;
  length firstcol 8 rectype $20 ;
  dlm=' ';
  infile tmpfile2 truncover dlm=dlm end=eof ;
  input @@ ;
  firstcol=verify(_infile_,' ');
  select (firstcol);
    when (4) do ;
      varnum + 1;
      input label $4-53 xlength start end xtype @@ ;
      if not eof then do until (firstcol ne 4) ;
        input / @@ ;
        firstcol=verify(_infile_,' ');
        if firstcol=4 then label=catx(' ',label,_infile_);
      end;
    end;
    when (6) do;
      rectype = left(scan(_infile_,1,':'));
      select (rectype);
        when ('SAS Name') do ;
          input @19 name ;
          output variables ;
        end;
        when ('Description') do ;
          input @19 description $90. @@;
          if not eof then do until (firstcol ne 19) ;
            input / @@ ;
            firstcol=verify(_infile_,' ');
            if firstcol=19 then description=catx(' ',description,_infile_);
          end;
        end;
        when ('VALUES') do until (firstcol ne 19) ;
          dlm='=';
          input @19 code decode $90. @@;
          output lookups;
          input / @@ ;
          firstcol=verify(_infile_,' ');
        end;
        otherwise do; put rectype= / _infile_ ; stop; end;
      end;
    end;
    otherwise do; put firstcol= / _infile_ ; stop; end;
  end;
run;

Example:

Obs varnum name                 xtype    xlength start end label

  1    1   PRVDR_CTGRY_SBTYP_CD VARCHAR2     2      1    2 Provider Category Subtype Code
  2    2   PRVDR_CTGRY_CD       VARCHAR2     2      3    4 Provider Category Code
  3    3   CHOW_CNT             NUMBER       2      5    6 CHOW Count
  4    4   CHOW_DT              DATE         8      7   14 CHOW Date
  5    5   CITY_NAME            VARCHAR2    28     15   42 Address: City

Obs description

  1 Identifies the subtype of the provider, within the primary category.  Used in reportin
  2 Identifies the type of provider participating in the Medicare/Medicaid program.
  3 Number of times this provider has undergone a change of ownership.
  4 Effective date of the most recent change of ownership for this provider.
  5 City in which the provider is physically located.

  

 Obs    varnum            name            code     decode

   1       1      PRVDR_CTGRY_SBTYP_CD    01       CLIA88 Laboratory
   2       2      PRVDR_CTGRY_CD          22       CLIA Laboratory
   3       7      CMPLNC_STUS_CD          A        IN COMPLIANCE
   4       7      CMPLNC_STUS_CD          B        NOT IN COMPLIANCE
   5      13      INTRMDRY_CARR_CD        00000    DUMMY FOR MEDICAID HHA
   6      13      INTRMDRY_CARR_CD        00010    BLUE CROSS (ALABAMA)
   7      13      INTRMDRY_CARR_CD        00011    CAHABA
   8      13      INTRMDRY_CARR_CD        00020    BLUE CROSS (ARKANSAS)
   9      13      INTRMDRY_CARR_CD        00040    BLUE CROSS (CALIFORNIA)
  10      13      INTRMDRY_CARR_CD        00060    BLUE CROSS (CONNECTICUT)
numediaweb
Calcite | Level 5
Thanks @Tom for taking time to answer and the great example you provided.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3004 views
  • 1 like
  • 4 in conversation