SAS | SAS DATA SET | SASPY I DATA TYPE of the COLUMN

[Narendra5005]

hello - what's the best way to validate the data types of the sas data set's columns?

[tomweber-sas]

Not sure on'validate', but you can see what the columns are using either the columnInfo() or contents() methods of the SASdata object.

Can you describe what you mean by 'validate' them?

Thanks, Tom

[Narendra5005]

any quick example with a proc sql query or any other way pls?

Say I have a table SALARY with the COLUMN as EMPLOYEE ...

I tried few ways but unable to fetch the exact data type

[tomweber-sas]

Are you asking how to programmatically check the type (Num or Char) of a given variable (column) of a SAS data set?

[Narendra5005]

yes Tom

[tomweber-sas]

ok, so there's more than one way, but how about this, for starters. the columnInfo() method returns a concise set of info for the columns, and in a pandas dataframe bydefault. So, to check a single column you could code the following; I'll use the cars dataset for a working example.

>>> cars = sas.sasdata('cars','sashelp')
>>> cars
Libref  = sashelp
Table   = cars
Dsopts  = {}
Results = Pandas

# this will returna dataframe you can query, if you know how to mess with the dataframe that way
>>> cars.columnInfo()
          Member  Num     Variable  Type  Len  Pos    Format            Label
0   SASHELP.CARS    9    Cylinders   Num    8   24       NaN              NaN
1   SASHELP.CARS    5   DriveTrain  Char    5  147       NaN              NaN
2   SASHELP.CARS    8   EngineSize   Num    8   16       NaN  Engine Size (L)
3   SASHELP.CARS   10   Horsepower   Num    8   32       NaN              NaN
4   SASHELP.CARS    7      Invoice   Num    8    8  DOLLAR8.              NaN
5   SASHELP.CARS   15       Length   Num    8   72       NaN      Length (IN)
6   SASHELP.CARS   11     MPG_City   Num    8   40       NaN       MPG (City)
7   SASHELP.CARS   12  MPG_Highway   Num    8   48       NaN    MPG (Highway)
8   SASHELP.CARS    6         MSRP   Num    8    0  DOLLAR8.              NaN
9   SASHELP.CARS    1         Make  Char   13   80       NaN              NaN
10  SASHELP.CARS    2        Model  Char   40   93       NaN              NaN
11  SASHELP.CARS    4       Origin  Char    6  141       NaN              NaN
12  SASHELP.CARS    3         Type  Char    8  133       NaN              NaN
13  SASHELP.CARS   13       Weight   Num    8   56       NaN     Weight (LBS)
14  SASHELP.CARS   14    Wheelbase   Num    8   64       NaN   Wheelbase (IN)

# now, let's only pick the one column - could just navigate the dataframe above to get to the column you want too
>>> cars.dsopts={'keep':'Make'}
>>> cars.columnInfo()
         Member  Num Variable  Type  Len  Pos
0  SASHELP.CARS    1     Make  Char   13   80
>>> df = cars.columnInfo()
>>> df['Type'][0]
'Char'
>>>
>>> df['Type'][0] == 'Char'
True
>>> df['Type'][0] == 'Num'
False
>>>

Thats one way you can get at the variable type programmatically. If you know how to navigate the DF, thenyou can just get the whole result and look. Getting only one column back inthe second case, and cheking it is easy if you just want to know about a particular column.

Is this what you are looking for?

Tom

[Narendra5005]

Thanks for the detailed info Tom.. let me try and get back.. When I tried last time with dataframes, all varchars were coming as objects.. will try this

[Narendra5005]

Hi Tom,

I got a question, there is a SAS portal known as SAS RFW where users create a project and execute the flow. Is there a way to automate this through an api or a proc sql query for SAS EG. We want to avoid the interaction with the web RFW portal. Please let me know if you are aware of anything around this. Thank you

Regards Narendra From: Tom Weber notifications@github.com Sent: Tuesday, June 30, 2020 9:09 PM To: sassoftware/saspy saspy@noreply.github.com Cc: Pendem, Narendhra npendem@deloitte.com; Author author@noreply.github.com Subject: [EXT] Re: [sassoftware/saspy] SAS | SAS DATA SET | SASPY I DATA TYPE of the COLUMN (#303)

ok, so there's more than one way, but how about this, for starters. the columnInfo() method returns a concise set of info for the columns, and in a pandas dataframe bydefault. So, to check a single column you could code the following; I'll use the cars dataset for a working example.

cars = sas.sasdata('cars','sashelp')

cars

Libref = sashelp

Table = cars

Dsopts = {}

Results = Pandas

this will returna dataframe you can query, if you know how to mess with the dataframe that way

cars.columnInfo()

      Member  Num     Variable  Type  Len  Pos    Format            Label

0 SASHELP.CARS 9 Cylinders Num 8 24 NaN NaN

1 SASHELP.CARS 5 DriveTrain Char 5 147 NaN NaN

2 SASHELP.CARS 8 EngineSize Num 8 16 NaN Engine Size (L)

3 SASHELP.CARS 10 Horsepower Num 8 32 NaN NaN

4 SASHELP.CARS 7 Invoice Num 8 8 DOLLAR8. NaN

5 SASHELP.CARS 15 Length Num 8 72 NaN Length (IN)

6 SASHELP.CARS 11 MPG_City Num 8 40 NaN MPG (City)

7 SASHELP.CARS 12 MPG_Highway Num 8 48 NaN MPG (Highway)

8 SASHELP.CARS 6 MSRP Num 8 0 DOLLAR8. NaN

9 SASHELP.CARS 1 Make Char 13 80 NaN NaN

10 SASHELP.CARS 2 Model Char 40 93 NaN NaN

11 SASHELP.CARS 4 Origin Char 6 141 NaN NaN

12 SASHELP.CARS 3 Type Char 8 133 NaN NaN

13 SASHELP.CARS 13 Weight Num 8 56 NaN Weight (LBS)

14 SASHELP.CARS 14 Wheelbase Num 8 64 NaN Wheelbase (IN)

now, let's only pick the one column - could just navigate the dataframe above to get to the column you want too

cars.dsopts={'keep':'Make'}

cars.columnInfo()

     Member  Num Variable  Type  Len  Pos

0 SASHELP.CARS 1 Make Char 13 80

df = cars.columnInfo()

df['Type'][0]

'Char'

df['Type'][0] == 'Char'

True

df['Type'][0] == 'Num'

False

Thats one way you can get at the variable type programmatically. If you know how to navigate the DF, thenyou can just get the whole result and look. Getting only one column back inthe second case, and cheking it is easy if you just want to know about a particular column.

Is this what you are looking for?

Tom

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://secure-web.cisco.com/1Y7P1TAjsHSkMy8qEAUhyk9gqegI4-V34EtT1i7kUaIIQSfgL5_w9vuOQ2NkWw7IVVoZ6eX-9b1lkV-1Df6ctV_vLTiXzbO6Tv272tpWYYBuw35eqYxd1gayidBoaxIJe_63cJA7En5KmH7Y7_Eiwy68sKJ0rjYebwP0_55XDvvUVKg127cR5tK_ODPLH1MtB_oeI5NhSYFMouYkMcOfIGHzq6vfM_xr6MU9h5aI3jH3c9ZndY7_Kt1T6tbXCrjWrobaySCQUvjtNzMAEsaq5Yopodkn87TzROirN7olq4Q6YBKsdLgQ5gFPdPUbnwwAoYomoKw8jqiH4ro2XxLND2854ic7Xh4DiNlM9qnzkQes3sPaXjkSQTfO5DIK2GScvz_lSTneT-S8yX7LjmlzflepDkMXJKh0wNimkA53GOYvGmkHo5Jhd3Mhj-nlykr4LU495ilexhTWTSB_54qHNqtuHS9oogvNAyNmYUWFEn3MY4zpNT9XCI-52RtMx3ZioB6fyBsEEGzqZfJEk8Hcacw/https%3A%2F%2Fgithub.com%2Fsassoftware%2Fsaspy%2Fissues%2F303%23issuecomment-651874831, or unsubscribehttps://secure-web.cisco.com/1DgSHnQfxfc2MW-PTBzVDvqzlt7Ys9jR_-aEkq0BkLcIiS9qFAAFwfr9wadsheecASNC8SMLMNaA6Z1TDw2u8j3R_DHNZyQnrhXLRMK6C3Z6kxhyJLoYCydkAX4Pag5NJ-2JWUnqzN_OmfoeUoq4FZJM-vqmubf47MjNfb7or2il49lo5IvD9po8Tr6VJJOAscVr1K_lXuc8OqpOSuT33lydJp3Jg6AOaOb_SFAVLTVbfn7zIHH4wzkPeAxNgwMq9EJoruu2Gh5uTvLZpcCHaZzIUFMakYmfH7pmhot4YDNAA_Nx1O1pZdXnj0wIH_6OySmOUSH9IWhSm49TcY56ofSpq7EeyiUeFaxMFkFMoLIddSuymLng6nxMCAyd5mRrpRQwyvCH0l-HjvL2RZ9mUqTmQEIOmxCRnVw46lV6hpUQpWw-ffqO4M_4yAzdZqWzXMZWKUJF9RcC4_3Anac5HbAkzEBUKVBhl-OcG_oCN74R6W4oB_Ab09rtgnraM7ZdexFAw01ckimHnjaDGX0ik3w/https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAPOEVZEVYWQY5VJV2FLNUTLRZIBKTANCNFSM4OMK6PUA.

This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and any disclosure, copying, or distribution of this message, or the taking of any action based on it, by you is strictly prohibited.

Deloitte refers to a Deloitte member firm, one of its related entities, or Deloitte Touche Tohmatsu Limited ("DTTL"). Each Deloitte member firm is a separate legal entity and a member of DTTL. DTTL does not provide services to clients. Please see www.deloitte.com/about to learn more.

v.E.1

[tomweber-sas]

Is SAS Risk and Finance Workbench what you are asking about? I had to google SAS RFW, and that's what the first hit was. I'm afraid I don't know anything about that. I would think Tech Support would be the fastest way to get an answer on that. Tom

[tomweber-sas]

Closing up old, idle issues. if you still need something on this, you can reopen it. If you need anything else, feel free to open a new issue. Thanks! Tom