dataset

[librasantosh]

How can I create data set with below data

variable names are

acctno|actdt|deactdt|deactreason|goodcredit|rateplan|dealertype|AGE|Province|sales

data
1176913194483|06/20/1999|||0|1|A1|58|BC|128
1176914599423|10/04/1999|10/15/1999|NEED|1|1|A1|45|AB|72
1176951913656|07/01/2000|||0|1|A1|57|BC|593
1176954000288|05/30/2000|||1|2|A1|47|ON|83

Can anyone help to create data set?

[librasantosh]

Data is

1176913194483 06/20/1999 0 1 A1 58 BC $128.00
1176914599423 10/04/1999 10/15/1999 NEED 1 1 A1 45 AB $72.00
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON

[Jagadishkatam]

Could you please provide the variable names with the data and the query with expected output

[Kurt_Bremser]

---

@librasantosh wrote:

Data is

1176913194483 06/20/1999 0 1 A1 58 BC $128.00
1176914599423 10/04/1999 10/15/1999 NEED 1 1 A1 45 AB $72.00
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON

---

And?

[librasantosh]

how can I show the acctno is unique? what will be the code to see the number of accounts activated and deactivated? Also the earliest and latest activation/deactivation dates available.

Can anyone give me code.

[Jagadishkatam]

Could you please provide sample data and expected output to get better response

[Shmuel]

@librasantosh, please pay attention:

 Is this post an answer to your previous post:

  https://communities.sas.com/t5/SAS-Procedures/data/m-p/385168

 Assuming the answer is YES then:

1) There is no need to open a new thread of posts. Just use REPLY to continue the thread

2) You were asked by @Jagadishkatam to provide variable names, so we have a common language to talk about.

3) I suggest you reply to this post, providing code using the {i} icon or the man in a square icon, like that:

data have;
    input var1 var2 mmddyy10. var3 ...;    /* addapt logic names to variables */
datalines;
1176913194483 06/20/1999 0 1 A1 58 BC $128.00
1176914599423 10/04/1999 10/15/1999 NEED 1 1 A1 45 AB $72.00
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON
; run;

4) What is the issue you have? What kind of help you need ?

[librasantosh]

I have tried same method given by you, but data out showing values in different column. 
Forxample, for one row entries are in different then other row entry. 

I hope you have understand my question
data have;
    input var1 var2 mmddyy10. var3 ...;    /* addapt logic names to variables */
datalines;

[Kurt_Bremser]

Please post the WHOLE step including the datalines, and indicate where the resulting dataset did not meet your expectations. If the step failed, post the (once again, whole) log.

[Jagadishkatam]

Please create a sample txt file as attached and run the below code with the location of sample where you save it

proc import datafile='~path\sample.txt' out=sample dbms=dlm replace;
getnames=yes;
delimiter='|';
run;

[Kurt_Bremser]

I merged those seemingly related posts into one.

To @librasantosh:

When you posted your question(s), you saw this right below the posting window:

Stop right there! Before pressing POST, tick off this checklist. Does your post …

✔ Have a descriptive subject line, i.e., How do I ‘XYZ’?

✔ Use simple language and provide context? Definitely mention what version you’re on.

✔ Include code and example data? Consider using the SAS Syntax feature.

You did not

- have a descriptive subject line

- use understandable language that makes it easy for others to understand; keep in mind that most of us use English as a common language without being native English speakers themselves. Also provide context.

- include the code you already tried, and data in a usable form. If you have trouble writing a simple datastep with some datalines, use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your dataset to a data step and post that here in a code window (7th icon above the posting window)

[librasantosh]

Below is the code I have used for data creation. But table giving values to different place.

Data KRM;
Input acctno  actdt mmddyy10.  deactdt mmddyy10.  deactreason$  goodcredit  rateplan  dealertype$  AGE  Province$ sales;

Datalines;
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON
1176992889500 11/28/2000 1 1 C1 68 AB $72.00
1177000067271 12/23/1999 0 1 B1 75 ON $134.00
1177028996676 09/21/2000 0 1 C1 QC $179.00
1177057406016 09/21/2000 0 1 C1 50 ON $529.00
1177066422248 04/26/1999 01/15/2001 NEED 0 1 A2 55 NS $44.00
1177113886410 09/13/2000 01/08/2001 COMP 0 1 C1 45 ON $63.00
1177128264924 12/10/1999 1 1 B1 38 ON $178.00
;
run;

[Kurt_Bremser]

---

@librasantosh wrote:

Below is the code I have used for data creation. But table giving values to different place.

Data KRM;
Input acctno  actdt mmddyy10.  deactdt mmddyy10.  deactreason$  goodcredit  rateplan  dealertype$  AGE  Province$ sales;

Datalines;
1176951913656 07/01/2000 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 1 1 A1 77 ON
1176992889500 11/28/2000 1 1 C1 68 AB $72.00
1177000067271 12/23/1999 0 1 B1 75 ON $134.00
1177028996676 09/21/2000 0 1 C1 QC $179.00
1177057406016 09/21/2000 0 1 C1 50 ON $529.00
1177066422248 04/26/1999 01/15/2001 NEED 0 1 A2 55 NS $44.00
1177113886410 09/13/2000 01/08/2001 COMP 0 1 C1 45 ON $63.00
1177128264924 12/10/1999 1 1 B1 38 ON $178.00
;
run;

---

When you read data using delimited (list) input, you have to provide something to be read when values are missing. SAS uses a single dot for this.

Note that you should use the colon right in front of the informat, so SAS does not switch to formatted (fixed-column) input, which requires all character columns to be present (in your case, 10 for the mmddyy10. format).

Finally, the truncover option allows missing values at the end of a dataline, so that SAS does not skip to the next line.

data KRM;
infile cards truncover;
input
  acctno :$13.
  actdt :mmddyy10.
  deactdt :mmddyy10.
  deactreason$
  goodcredit
  rateplan
  dealertype$
  AGE
  Province$
  sales :dollar10.2
;
format
  actdt deactdt mmddyy10.
;
datalines;
1176951913656 07/01/2000 . . 0 1 A1 57 BC $593.00
1176954000288 05/30/2000 . . 1 2 A1 47 ON $83.00
1176969186303 12/13/2000 . . 1 1 C1 82 BC
1176991056273 08/31/1999 09/18/2000 MOVE 1 1 C1 92 QC $1041.00
1176991866552 05/24/2000 . . 1 1 A1 77 ON
1176992889500 11/28/2000 . . 1 1 C1 68 AB $72.00
1177000067271 12/23/1999 . . 0 1 B1 75 ON $134.00
1177028996676 09/21/2000 . . 0 1 C1 . QC $179.00
1177057406016 09/21/2000 . . 0 1 C1 50 ON $529.00
1177066422248 04/26/1999 01/15/2001 NEED 0 1 A2 55 NS $44.00
1177113886410 09/13/2000 01/08/2001 COMP 0 1 C1 45 ON $63.00
1177128264924 12/10/1999 . . 1 1 B1 38 ON $178.00
;
run;

[librasantosh]

I agree with these codes, but If I have huge data then How will I put '.' for the all missing values. This has more than thousand observation. Is there any way for this one? Thanks alot for reply

[Kurt_Bremser]

There's two possible scenarios:

You are reading your data from an external source. If that is the case, such data must have a reliable structure that allows SAS to detect missing values (two delimiters in succession, for instance)

Or you are preparing a dataset you already have for posting as example here. In that case, you should post just enough observations to illustrate your issue, and you can use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to automatically create a data step from your dataset that has ALL attributes and contents.

Download the .zip, extract the code, open the code with EG (or an editor and copy/paste it to SAS Studio), and run it once to compile the macro. Then use as documented.

If you prepare example data manually, taking care of missing values is no problem. Just look at the time it took me to correct your code.