11-01-2018
Rookie_123
Fluorite | Level 6
Member since
08-10-2018
- 13 Posts
- 0 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by Rookie_123
Subject Views Posted 2311 11-01-2018 08:04 AM 2454 10-28-2018 10:09 AM 2461 10-28-2018 09:57 AM 2484 10-28-2018 09:05 AM 11852 10-02-2018 02:05 PM 11861 10-02-2018 01:56 PM 11875 10-02-2018 01:38 PM 2315 09-14-2018 11:38 AM 2327 09-14-2018 11:03 AM 2333 09-14-2018 10:52 AM -
Activity Feed for Rookie_123
- Posted Re: Yearly conditional sum in SAS on SAS Programming. 11-01-2018 08:04 AM
- Posted Re: Yearly conditional sum in SAS on SAS Programming. 10-28-2018 10:09 AM
- Posted Re: Yearly conditional sum in SAS on SAS Programming. 10-28-2018 09:57 AM
- Posted Yearly conditional sum in SAS on SAS Programming. 10-28-2018 09:05 AM
- Posted Re: Convert number to date on SAS Programming. 10-02-2018 02:05 PM
- Posted Re: Convert number to date on SAS Programming. 10-02-2018 01:56 PM
- Posted Convert number to date on SAS Programming. 10-02-2018 01:38 PM
- Posted Re: Reading access database with infile statement on SAS Programming. 09-14-2018 11:38 AM
- Posted Re: Reading access database with infile statement on SAS Programming. 09-14-2018 11:03 AM
- Posted Re: Reading access database with infile statement on SAS Programming. 09-14-2018 10:52 AM
- Posted Reading access database with infile statement on SAS Programming. 09-14-2018 09:23 AM
- Posted Re: Changing variable Format with proc import on SAS Programming. 08-16-2018 02:06 PM
- Posted Changing variable Format with proc import on SAS Programming. 08-16-2018 12:54 PM
- Tagged Changing variable Format with proc import on SAS Programming. 08-16-2018 12:54 PM
- Tagged Changing variable Format with proc import on SAS Programming. 08-16-2018 12:54 PM
- Tagged Changing variable Format with proc import on SAS Programming. 08-16-2018 12:54 PM
- Tagged Changing variable Format with proc import on SAS Programming. 08-16-2018 12:54 PM
11-01-2018
08:04 AM
Everyone has helped in their own way to achieve what I want.Thanks for your help
... View more
10-28-2018
10:09 AM
I wanted to avoid PROC SQL part.Do it entirely in SAS without using PROC SQL at all
... View more
10-28-2018
09:05 AM
I have a below table +------+------+------+------+------+-----+
| Yr | col1 | col2 | col3 | col4 | PQR |
+------+------+------+------+------+-----+
| 2012 | 1 | 0 | 1 | 1 | 2 |
| 2012 | 0 | 1 | 0 | 0 | 4 |
| 2013 | 1 | 1 | 1 | 1 | 6 |
| 2014 | 0 | 0 | 0 | 0 | 8 |
| 2012 | 1 | 0 | 1 | 1 | 7 |
| 2013 | 0 | 1 | 0 | 0 | 3 |
| 2014 | 1 | 0 | 1 | 1 | 2 |
| 2012 | 0 | 1 | 0 | 0 | 10 |
| 2014 | 0 | 0 | 1 | 0 | 12 |
| 2014 | 0 | 0 | 0 | 0 | 5 |
+------+------+------+------+------+-----+ The output I want is as below +------+-------+------+------+------+
| | Total | 2012 | 2013 | 2014 |
+------+-------+------+------+------+
| col1 | 17 | 9 | 6 | 2 |
| col2 | 23 | 14 | 9 | 0 |
| col3 | 29 | 9 | 6 | 14 |
| col4 | 17 | 9 | 6 | 2 |
+------+-------+------+------+------+ For row `col1` in my output table The column `Total` is `SUM(PQR)` when `col1` is 1 my input table The value `17` is `SUM(PQR)` when `col1` is 1 in my input table The value in col `2012` is `SUM(PQR)` when `col1` is 1 and `Yr=2012` in my input table The value `9` is `SUM(PQR)` when `col1` is 1 and `Yr=2012` in my input table Similarly `6` in column `2013` is `SUM(PQR)` when `col1` is 1 and `Yr` is `2013` Hope the process to get output table is understood Any help will be appreciated
... View more
10-02-2018
02:05 PM
Thanks the approach mentioned in the answer is perfect for my usecase
... View more
10-02-2018
01:56 PM
I read the date from Access database with PROC IMPORT and the data itself is in the format YYYYMMDD. So I think while reading itself I will need to reverse the observations to be read as DD/MM/YYYY I dont know if that is possible or not
... View more
10-02-2018
01:38 PM
I have a column/variable named Release_Date with length 8 format 11. and informat 11. The observation in that column are in the form YYYYMMDD. SAS actually reads them as pure number I want to convert those observation in DD/MM/YYYY format with format and informat as DDMMYY10. For example Release_Date is 20180612 then I want 12/06/2018 Any help with this regard will be appreciated
... View more
09-14-2018
11:38 AM
When I follow the link to connect to Access from SAS. I get the error NOTE: Statement not executed due to NOEXEC option.
... View more
09-14-2018
11:03 AM
My main aim is whatever is in Access Database.I should get it in SAS database when I import.Basically I am not understanding that how SAS is setting 255 for character variables. Is it because max length is 255 in the access database or is it because that's the max length it can set(Even if access database has say 300 in it)
... View more
09-14-2018
10:52 AM
The database is so large that I am not able to find its length in Access.I cant run any code on it to find its length outside SAS
... View more
09-14-2018
09:23 AM
TLDR: I want to read my access database via infile statement to have complete control over format,informat,length of variables/columns I have an access database which has around 2 million records.Currently I am importing it with PROC IMPORT. The problem: From what I have read that PROC IMPORT uses around 32k rows to set format,informat,length for variables I feel that data may be getting truncated. Another problem is all my character variables have format and informat as $255.and length as 255.I dont want my all variables to be of this length,format,informat. Some I want to be more than 255 and some to be less than that. My current code: PROC IMPORT OUT= Input.access_db
DATATABLE= "DATABASE"
DBMS=ACCESSCS REPLACE;
DATABASE="\\Input\Entire_Records.accdb";
SCANMEMO=YES;
USEDATE=YES;
SCANTIME=NO;
RUN; I want to do above with an infile statement. I have a code to import my csv via infile statement.But I am not getting how do I modify it to make it to read my access database data Input.Real;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'Input\Real.csv'
delimiter = ','
MISSOVER
DSD
lrecl=32767
firstobs=2 ;
informat DATE 11.;
informat IDENTIFIER $255. ;
informat LINE_CODE $255. ;
informat ACTION $255. ;
format DATE 11.;
format IDENTIFIER $255. ;
format LINE_CODE $255. ;
format ACTION $255. ;
input
DATE
IDENTIFIER $
LINE_CODE $
ACTION $ ;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
RUN;
... View more
08-16-2018
02:06 PM
Can you please show a sample code of datastep to change variable format, informat, length, type
... View more
08-16-2018
12:54 PM
TLDR: How to specify column format, informat, lenght, type in PROC IMPORT.My database is in xlsx format Longer Version I am importing my 3 excel databases A, B, C PROC IMPORT. I want to append this datasets after importing. These databases consists of date variables in different columns Database A will have values in column DateA Database B will have values in column DateB Database C will have values in column DateC All the three database will have columns DateA, DateB, DateC.But values in those columns will depend on what database it is. So when I import Database A the column type of DateB and DateC is set to character since they dont have values in them.How can I specify at the import itself that DateB and DateC should be considered as Date type. Another problem is the lenghts, format, informat of columns is set as it is in Database A.Due to which when I try to append B with A it gives me a truncation warning.I dont want my data to be truncated. I have in all 50 columns so manually specifying each columns lenght, format, informat etc. wont be a feasible thing.Is there any better alternative
... View more