03-27-2020
AshishM
Fluorite | Level 6
Member since
03-21-2020
- 9 Posts
- 3 Likes Given
- 0 Solutions
- 3 Likes Received
-
Latest posts by AshishM
Subject Views Posted 1546 03-27-2020 12:56 AM 1574 03-25-2020 12:51 AM 10234 03-23-2020 11:04 PM 10260 03-23-2020 06:27 PM 10322 03-22-2020 10:55 PM 10373 03-21-2020 09:19 PM 10386 03-21-2020 08:15 PM 10393 03-21-2020 07:55 PM 10526 03-21-2020 06:31 AM -
Activity Feed for AshishM
- Posted Re: saspy dataframe to sasdataset outfmts on Developers. 03-27-2020 12:56 AM
- Liked Re: saspy dataframe to sasdataset outfmts for sastpw. 03-27-2020 12:47 AM
- Posted Re: saspy dataframe to sasdataset outfmts on Developers. 03-25-2020 12:51 AM
- Liked Re: saspy dataframe to sasdataset outfmts for sastpw. 03-25-2020 12:43 AM
- Posted Re: saspy dataframe to sasdataset outfmts on Developers. 03-23-2020 11:04 PM
- Liked Re: saspy dataframe to sasdataset outfmts for sastpw. 03-23-2020 09:43 PM
- Posted Re: saspy dataframe to sasdataset outfmts on Developers. 03-23-2020 06:27 PM
- Posted Re: saspy dataframe to sasdataset outfmts on Developers. 03-22-2020 10:55 PM
- Posted Re: saspy dataframe to sasdataset outfmts on Developers. 03-21-2020 09:19 PM
- Got a Like for Re: saspy dataframe to sasdataset outfmts. 03-21-2020 08:16 PM
- Posted Re: saspy dataframe to sasdataset outfmts on Developers. 03-21-2020 08:15 PM
- Posted Re: saspy dataframe to sasdataset outfmts on Developers. 03-21-2020 07:55 PM
- Got a Like for saspy dataframe to sasdataset outfmts. 03-21-2020 01:06 PM
- Posted saspy dataframe to sasdataset outfmts on Developers. 03-21-2020 06:31 AM
-
Posts I Liked
Subject Likes Author Latest Post 1 1 1 -
My Liked Posts
Subject Likes Posted 2 03-21-2020 08:15 PM 1 03-21-2020 06:31 AM
03-27-2020
12:56 AM
Awesome!!! thanks exactly what I would need as a beginner. Have bookmarked all of them.
... View more
03-25-2020
12:51 AM
Hi Tom, Thanks a lot for all your help and guidance. I went through your original post again and came up with the same approach. I referred to the same link to come to up with the same solution :). I am new to Pandas, SAS and python so learning them slowly and spending a lot of time to solve issues. I am now stuck with another issue, will try my best to fix it if not then I'll post it again. Thanks again for your time. Much appreciated. Ashish
... View more
03-23-2020
11:04 PM
Hi Tom, Thanks a lot, that was really informative, I was able to load that file into SAS. Now I am stuck on the next file which has few columns that are giving issues : 1. Col1 has the value as shown below. When I try converting it to datetime my_dataset['Col1'] = pd.to_datetime(my_dataset['Col1']). I get ValueError: Unknown string format. I have shown the value in this column below. dtype: object Col1 object Col2 int64 2. Col2 was identified as int64. However I want to load it as a character. How can I modify the type from numeric to object? [Resolved]: I used the following code to cast it to object my_dataset['Col1']=my_dataset.astype({'Col1': 'object'}). Seems to be working fine. Col1 0 20NOV2007:00:00:00 1 20NOV2007:00:00:00 2 20NOV2007:00:00:00 3 20NOV2007:00:00:00 4 20NOV2007:00:00:00
... View more
03-23-2020
06:27 PM
Hi Tom, I understand that the syntax used for datetimes and outfmts (with format keyword) I used is incorrect but surprisingly it works and populates the SAS dataset, using the syntax provided for df2sd i.e without format keyword is resulting in an empty dataset in SAS. Please find the dtypes and values in sas dataset below: Col1 int64 Col2 object Col3 object Col4 float64 Col5 object Col6 object Col7 int64 Col8 object Col9 object dtype: object Col1 Col2 Col3 \ 0 201605141 TRN201605141 PJ 1 201605142 TRN201605142 PJ 2 201605143 TRN201605143 PJ 3 201605144 TRN201605144 PJ 4 201605145 TRN201605145 PJ Col4 Col5 Col6 Col7 \ 0 1000 CREDIT 15-Jul-19 102356 1 1000 CREDIT 15-Jul-19 102356 2 1000 CREDIT 15-Jul-19 102356 3 1000 CREDIT 15-Jul-19 102356 4 1000 CREDIT 15-Jul-19 102356 Col8 Col9 0 UNK UNK 1 UNK UNK 2 UNK UNK 3 UNK UNK 4 UNK UNK
... View more
03-22-2020
10:55 PM
Hi Tom, The problem is that SAS attached character format to date variable with value 15-Jul-19. Now since it is character I am unable to change the format to date9. The following code which I am using to convert to dataset already specifies column 9 as date9. but that was ignored just like other column formats specified. my_sas_dataset_inv = sas.df2sd(my_dataset_inv,table=tablename,libref='Trial',datetimes={'Col9' : 'date9.'}, outfmts={'Col2:'13.', 'Col3':'dollar50.', 'Col4':'dollar35.','Col5':'26.5','Col6':'dollar20.','TIME_KEY':'6.', 'Col7:'dollar50.', 'Col8':'dollar50.'})
... View more
03-21-2020
09:19 PM
Hi Tom, This is the code that I am using, some of the things are sensitive so I have masked them. Basically the input folder from where I am reading the .dat files(comma separated) has 2 different kinds of files, each file has different structure i.e tables/data which is why you'll see the if else statement. I am facing the same issue for both so I had posted just one part of the code. I am not explicitly defining any data type when I am reading from CSV(I believe it will read it as string), I am thinking of defining dtype in read csv, though it should not have been required if the format worked properly while generating the dataset. I am trying to define the format when the output SAS dataset is created, however it doesn't understand the format and defines the format based on the actual data length of the data in tables. I am not getting any errors, if there's a way to display the error logs for df2sd method, the kindly suggest. Dataset looks exactly the same as the data in CSV file, some of the data like column names etc is sensitive so I have masked it. If I remove the format keyword as mentioned in last post then it results in empty dataset in SAS and since I don't know how to get logs for this method I can't view what error is thrown if it is being thrown. Python does not throw any syntax errors both ways which is surprising as this is not what the documentation shows. I am using the below method for printing errors print(my_sas_dataset_inv.HTML) but currently I don't see any errors for that. Code: import pandas as pd import saspy import os saspy.SAScfg sas = saspy.SASsession(cfgfile='C:\\Program Files\\Anaconda3\\lib\\site-packages\\saspy\\sascfg_personal.py') Col_party=['Col1',' Col2', 'Col3', …..] Col_party_inv=['Col4', 'Col5', 'Col6',...] path='C:\\Users\\dummyuser\\Desktop\\trial' files = os.listdir(path) print(files[1]) for file in files: if ".dat" in file: print(file) filename=path+'\\'+file print('filename: '+filename) if "trn_inv" in file: Datekey=file[13:21] print('Datekey: '+Datekey) tablename='tbl1'+Datekey print('tablename: '+tablename) my_dataset_inv = pd.read_csv(filename,names=Col_party_inv,sep='|' , engine='python') my_sas_dataset_inv = sas.df2sd(my_dataset_inv,table=tablename,libref='TEMP',datetimes={'format':{'Col9' : 'date9.'}}, outfmts={'format':{'Col1:'13.', 'Col2':'dollar50.', 'Col3':'dollar35.','Col4':'26.5','Col5':'dollar20.','Col6':'6.', 'Col7':'dollar50.', 'Col8':'dollar50.'}}) my_sas_dataset_inv.head print(my_sas_dataset_inv.HTML) else: Datekey=file[9:17] print('Datekey: '+Datekey) tablename='tbl2'+Datekey print('tablename: '+tablename) my_dataset = pd.read_csv(filename,names=Col_party,sep='|' , engine='python') my_sas_dataset = sas.df2sd(my_dataset,table=tablename,libref='TEMP',datetimes={'format':{'Col9' : 'date9.'}}, outfmts={'format':{'Col1:'13.', 'Col2':'dollar50.', 'Col3':'dollar35.','Col4':'26.5','Col5':'dollar20.','Col6':'6.', 'Col7':'dollar50.', 'Col8':'dollar50.'}}) Output of above python code: filename.dat filename: C:\Users\dummyuser\Desktop\trial\filename.dat Datekey: XXXXX tablename: table1 0 {'LOG': "\x0c10 The SAS System 11:48 Sunday, March 22, 2020\n\n169 ods listing close;ods html5 (id=saspy_internal) file=_tomods1 options(bitmap_mode='inline') device=svg style=HTMLBlue;\n169 ! ods graphics on / outputfmt=png;\nNOTE: Writing HTML5(SASPY_INTERNAL) Body file: _TOMODS1\n170 \n171 \n172 The SAS dataset created.Properties are still the same as posted earlier.
... View more
03-21-2020
08:15 PM
2 Likes
Hi Tom, Thanks for your suggestion, I actually referred to the same page to come up with the code posted earlier. I have another very interesting observation which I believe is a bug but probably experts can point out. With the code I posted earlier, I observed that the data in the dataset was not populating (blank dataset). So I modified the code as below (notice the format keyword with outfmts), which is not how it should be as per the advanced page documentation and I was able to load the dataset with data however the format is still incorrect : my_sas_dataset_inv = sas.df2sd(my_dataset_inv,table=tablename,libref='MST_PREP',datetimes={'format':{'Col9' : 'date9.'}}, outfmts={'format':{'Col1':'13.', 'Col2':'dollar50.', 'Col3':'dollar35.','Col4':'26.5','Col5':'dollar20.','Col6':'6.', 'Col7':'dollar50.', 'Col8':'dollar50.'}}) The format keywork used above is actually the defined format for sas.sd2df_DISK or sd2df_CSV, instead of dsopts I used outfmts: df = sas.sd2df_DISK('cars', 'sashelp', dtype={'invoice' : 'int'}, my_fmts=True, dsopts={'keep' : 'MSRP Invoice', 'format' : {'msrp':'dollar32.2','invoice':'best32.'}}) This is how it should be as per documentation in advanced techniques: sd = sas.df2sd(df, datetimes={'d' : 'date', 't' : 'time'}, outfmts={'dt' : 'comma32.4', 'd' : 'YYMMDD.', 't' : 'TIMEAMPM.'}, results='text') Do you know how can I log a defect for the developer? Also, regarding printing the logs, I tried the code you provided but it didn't show any errors even when the dataset was empty. is there any other way to view the logs of this method?
... View more
03-21-2020
07:55 PM
Hi Tom, Thanks for the suggestion. You're right regarding DOLLAR 255 amount however reducing the number also didn't work. I tried your code and I still see the same issue. I also added the period and it still didn't work. Not sure how to check what I am doing wrong, have already spent hours on this issue which looked simple 😞
... View more
03-21-2020
06:31 AM
1 Like
Hi Experts, I have tried everything but it doesn't seem to work as expected. I am using saspy df2sd method to create a sasdataset from dataframe and want the columns in sas dataset output to be in the format shown below. I have tried everything in the examples but can't get it to work. Below is the statement that I am executing, it is executed successfully and dataset is created however the format is not as per the one defined below. I end up getting a dataset as shown below with incorrect types. can someone please suggest what am I doing wrong. my_sas_dataset_inv = sas.df2sd(my_dataset_inv,table=tablename,libref='trial',datetimes={'Col9' : 'DATE9'}, outfmts={'Col1':'13', 'Col2':'dollar50', 'Col3':'dollar35','Col4':'26.5','Col5':'dollar20','Col6':'6', 'Col7':'dollar255', 'Col8':'dollar50'}) Thanks in Advance
... View more